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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.