Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Chthonian
Helper III
Helper III

Filter 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 ManagerCustomer NameOpportunities
AM1Customer12
AM1Customer20
AM2Customer33

 

Data Model Layout:
CEAM Filter Contect.png

 

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

1 ACCEPTED SOLUTION
az38
Community Champion
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

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

2 REPLIES 2
Chthonian
Helper III
Helper III

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

isblank.png

az38
Community Champion
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

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors