Forum Discussion

flakycrust's avatar
flakycrust
Regular Visitor
4 years ago
Solved

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

  • rsbin's avatar
    rsbin
    Community Champion

    flakycrust ,

    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] )) + 0

    Hopefully, this gets you over the hump and on your way.

    Good Luck and Regards,

    • flakycrust's avatar
      flakycrust
      Regular 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

  • flakycrust's avatar
    flakycrust
    Regular 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?