Slicer to display column with 0 counts

[bluesky63]

Hi,
What is the DAX formula in Powerviot to count the column for a laptop model and return 0.

See the source and source_pp

https://onedrive.live.com/?cid=D249FD932335CE0E&id=D249FD932335CE0E!126

basically when I click on Model 4 for e.g. even thought the Bootup/shutdown/login range Qty is zero, still display zero

Bootup Range	(e.g)
<=25	4
>25&<=30	0
>30&<=35	168
>35&<=40	110
>40&<=45	70
>45&<=50	0
>50&<=55	49
>55&<=60	55
>60&<=120	0
> 120	40

<tbody>
</tbody>

what is the best way to do it, using DAX ? (can teach me how to code in PowerPivot)

 

[scottsen]

You would need to build a separate table w/ all the possible bootup ranges, and create a relationship back to your current fact table. Put fields from that NEW lookup table on rows, and keep using your existing Counts.

You will then briefly be sad it looks the same, but at that point you can edit the Pivot Table Options to say "show items with no items on rows/columns" ... and you will get what you want. Without the first step... I don't think this option is going to help you.

All that said... you might ALSO be able to just write a DAX Formula:
=IF (COUNTROWS(MyTable) = BLANK(), 0, COUNTROWS(MyTable))

(Edit: In hindsite, I don't think that will work either... until you split out your lookup to a separate table)

 

[bluesky63]

You would need to build a separate table w/ all the possible bootup ranges, and create a relationship back to your current fact table. Put fields from that NEW lookup table on rows, and keep using your existing Counts.

You will then briefly be sad it looks the same, but at that point you can edit the Pivot Table Options to say "show items with no items on rows/columns" ... and you will get what you want. Without the first step... I don't think this option is going to help you.

All that said... you might ALSO be able to just write a DAX Formula:
=IF (COUNTROWS(MyTable) = BLANK(), 0, COUNTROWS(MyTable))

(Edit: In hindsite, I don't think that will work either... until you split out your lookup to a separate table)

Hi scottsen

Thanks for your reply

I had create a second tab devices in the PowerPivot model, Can help in using DAX to count Boot range, Shutdown range and Login range of the seven HP models, including zero count.

thereafter, not sure whether can achieve what I want, i.e. display zero even the particular range is zero

Thanks.

 

[scottsen]

I believe you will need a table of just "Bootup Ranges", and put THAT on rows. Then you can get your zero's.

 

[bluesky63]

Hi scottsen,

I had imported in Range spreadsheet-Boot Range, and when clicking on HP model 4 slicer, zero still didn't appear
thanks

see my updated file in onedrive

 

[scottsen]

Great! Okay, now in your pivot table if you bring up the pivot table properties -- on the display tab you will find a "display items w/ no data on rows/columns" sort of checkbox. If you checked those... the rows would show up, but they would just be blank. But that is progress!

Regardless of that setting, now that we have a separate table and those fields are on rows, this will give what you want:
=IF (countrows(Performance) = BLANK(), 0, COUNTROWS(Performance))

 

[bluesky63]

Hi scottsen, thanks for your help, I manage to replace blank with zero by setting in pivot table properties > layout & format > format and check for empty cells show 0

Appreciate you can share me how to use calculated DAX command to count for each HP model range in boot range power pivot table (3rd tab), I had uploaded the new version to onedrive. thanks!

 

[bluesky63]

Hi scottsen, thanks for your help, I manage to replace blank with zero by setting in pivot table properties > layout & format > format and check for empty cells show 0

Appreciate you can share me how to use calculated DAX command to count for each HP model range in boot range power pivot table (3rd tab), I had uploaded the new version to onedrive. thanks!

manage to get it by

=CALCULATE(count(Performance[Boot Duration Sec]), All(Performance[Boot Range]))

See boot range table, but I got another problem, although I create a relationship between boot range and performance, by linking the boot range as key. When I drag boot range from performance, and boot count from boot range, I can't get the result that I want

https://onedrive.live.com/?cid=D249FD932335CE0E&id=D249FD932335CE0E!126

Please help to see how I can achieve it. thanks!

 

[scottsen]

Any time you are using "lookup tables" (these are the "one side" of the one to many relationships)... they are capable of filtering your data/fact table (the many side). What you need is columns from the lookup table to be on your pivot table if you expect them to filter.

If you are looking at the diagram view -- note that the arrows point towards the lookup tables (away from the fact table).

It's important to remember that "relationships have directions". See Power Pivot Relationships | Power Pivot | Tiny Lizard