topic Re: Filter Context Issue when trying to Show Records with No Data in DAX Commands and Tips

Filter Context Issue when trying to Show Records with No Data

Chthonian — Sat, 15 Feb 2020 06:54:01 GMT

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

Re: Filter Context Issue when trying to Show Records with No Data

Chthonian — Sat, 15 Feb 2020 07:00:33 GMT

🙄😣😴 - 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:

Re: Filter Context Issue when trying to Show Records with No Data

az38 — Sun, 16 Feb 2020 17:17:52 GMT

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

Re: Filter Context Issue when trying to Show Records with No Data

lokar78 — Wed, 29 May 2024 16:38:04 GMT

From the page you linked:

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

Re: Filter Context Issue when trying to Show Records with No Data

lokar78 — Wed, 29 May 2024 16:39:41 GMT

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

Total Opportunities QTY = COUNTROWS(Opportunities)+0