show items with no data

[bmm]

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'

OpenDate	01/02/2017
Row Labels	Count of Date Open
Academy2	49
Academy6	1
Academy7	15
Grand Total	65

excel pivot table after 'show items with no data'

OpenDate	01/02/2017
Row Labels	Count of Date Open
Academy1
Academy2	49
Academy3
Academy4
Academy5
Academy6	1
Academy7	15
Academy8
Academy9
Grand Total	65

[Eric_Zhang · Accepted solution]

@bmm

You will have to use a Academies table.

And map the proper relationship.

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

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