Forum Discussion
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'
| 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 |
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]))
1 Reply
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]))