Forum Discussion
How to Display Null Count on a Card Visual
Hi All,
I am doing a report for IT Tickets. I am trying to display the number of tickets that have no responses via name slicer; either [typeR] = null or [typeR] = "R". However, PowerBI is treating it as if the blanks don't exist. When I try to filter the card by blanks, only R comes up as an option.
I have tried COUNT(DISTINCT)\DISTINCTCOUNT() but it returns 1, I know there are 1035 blanks from a count in Power Query.
I have tried
NoResponseCount =
CALCULATE(COUNTROWS(ResponseMerge),FILTER(ResponseMerge,ISBLANK(ResponseMerge[typeR])))
But this returns nothing. The card says (BLANK). Many other measures I have tried return this as well, inlcuding the Quick Measure 'Filtered Value' that's built in.
I have also tried adding a binary variable where No Response = 0 and Response = 1 however, the 0's are not counted.
The only one that seems to work is
NoResponseCount = COUNTROWS(FILTER(ALL(ResponseMerge), ISBLANK(ResponseMerge[typeR]) || [TypeR]=""))
But the card displays 1036 when there are 1035 blanks. It also doesn't slice with the names. I know you will say "Are your table relationships correct?". They are because when I use
NoResponseCount = COUNTROWS(ALL([Table]) - COUNTROWS(FILTER([Table],[Table][typeR] = "R")) the slicer works, but the number that is displayed is incorrect when using the name slicer.
I thought I should mention that the nulls that are in [typeR] are also in every other column, I changed these to 0's instead but that still did not solve the problem. I also have no page nor report filters.
Any help would be greatly appreciated. I have been stuck on this for 2 days 😞
I duplicated the query and it recognized the blanks...... so if anyone every has a problem where their data isn't recognized, duplicate it I guess?
3 Replies
- rsbinCommunity Champion
This might not be the cleanest solution, but I have just resolved a similar issue.
I created a new Calculated Column as follows:
Is_YourColumn_Null = IF( ISBLANK( [YourColumn] ), "Null", BLANK() )Then you can use the following Measure:
Null_Count = CALCULATE( COUNTA( YourTable[Is_YourColumn_Null] )) + 0Hopefully, this gets you over the hump and on your way.
Good Luck and Regards,
- flakycrustRegular Visitor
Thanks for your reply. I did try this but the result is just 0. That's what I'm confused about. It's as if the blanks don't exist
- flakycrustRegular Visitor
I duplicated the query and it recognized the blanks...... so if anyone every has a problem where their data isn't recognized, duplicate it I guess?