Forum Discussion
Helper IIIFilter Context Issue when trying to Show Records with No Data
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
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
4 Replies
- 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
Community Champion
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
From the page you linked:
When the table argument contains no rows, the function returns BLANK.
To convert a BLANK() to a 0, just add a 0 to your expression.
Total Opportunities QTY = COUNTROWS(Opportunities)+0
