cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bmm
Regular Visitor

show items with no data

I have a table where I want to show data for for instance Feb 2017 but also a running total for all categories not just those with data in Feb 2017.  I have a slicer in Power BI to select the Feb 2017 data.  When this is selected I can only see categories that have data.  What I want to do is replicate the behaviour of a pivot table 'show items with no data'.  There seems to be an equivalent in Power BI 'show items with no data' but this does not work.

 

excel pivot table before 'show items with no data'

 

OpenDate01/02/2017
  
Row LabelsCount of Date Open
Academy249
Academy61
Academy715
Grand Total65

 

 

excel pivot table after 'show items with no data'

OpenDate01/02/2017
  
Row LabelsCount of Date Open
Academy1 
Academy249
Academy3 
Academy4 
Academy5 
Academy61
Academy715
Academy8 
Academy9 
Grand Total65
1 ACCEPTED SOLUTION
Eric_Zhang
Microsoft
Microsoft

@bmm

 

You will have to use a Academies table.

 

Capture.PNG

 

And map the proper relationship.

Capture.PNG

 

Then use a Measure to show 0 when the academy is missing.

CNT MEASURE = IF(ISBLANK(SUM(YourTable[cnt])),0,SUM(YourTable[cnt]))

Capture.PNG

View solution in original post

1 REPLY 1
Eric_Zhang
Microsoft
Microsoft

@bmm

 

You will have to use a Academies table.

 

Capture.PNG

 

And map the proper relationship.

Capture.PNG

 

Then use a Measure to show 0 when the academy is missing.

CNT MEASURE = IF(ISBLANK(SUM(YourTable[cnt])),0,SUM(YourTable[cnt]))

Capture.PNG

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!