Filter Context Issue when trying to Show Records with No Data

[Chthonian]

Hello my PowerBi friends, 

I have (hopefully) a simple filtering issue when I am trying to show records with no data in a Table visual and hoping somebody in the datasphere can help.

Scenario/Requirement:

Show a list of Account Managers, their customers, and a count of sales opportunities. This table also needs to show if there are no opportunities against the customer account. 

Example Required Result:

Account Manager	Customer Name	Opportunities
AM1	Customer1	2
AM1	Customer2	0
AM2	Customer3	3

Data Model Layout:

DAX Measure:

Total Opportunities QTY = IF(
   ISBLANK(COUNTROWS(Opportunities)),
   0, 
   COUNTROWS(Opportunities)
)

The Issue:

Using the above measure strips any filtering from Customer Accounts and shows all Customer Accounts against all Account Managers. The moment I remove the ISBLANK and IF it filters exactly as you would expect. Now I know I can simply use 'Show Items with No Data' from the field options, but I would like to use conditional formatting and was going to use "IF COUNT(Opportinities) = 0, SHOW RED ICON" as an example.

I am probably missing something simple in my tired state, but you never know if you dont ask 🤔

Thanks in advance,

David

[lokar78]

To convert a BLANK() to a 0, just add a 0 to your expression.

Total Opportunities QTY = COUNTROWS(Opportunities)+0

[Chthonian]

🙄😣😴 - So, I am indeed being a numpty!! Conditional formatting caters for 'is Blank' I am a silly muppet, so sorry folks 😅

While I have solved my issue in this instance, I would be interested to know why filters are stripped using the ISBLANK() dax in a measure, so if anybody is in a teaching enlightening mood, my brain is always eager to understand these things.

SOLUTION:

[az38 · Accepted solution]

Hi @Chthonian 

COUNTROWS() can not return you blank. If no rows founded it will return you 0, as described here https://docs.microsoft.com/en-us/dax/countrows-function-dax

so, you should compare it with 0 in if, like

Total Opportunities QTY = IF(
   COUNTROWS(Opportunities) = 0,
   0, 
   COUNTROWS(Opportunities)
)

but it has no sense, I think 🙂

so,for conditional formatting it could be less strictly condition

[lokar78]

From the page you linked:

• When the table argument contains no rows, the function returns BLANK.