cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RobinNeven
Frequent Visitor

Hide rows with blank values for measure depending on slicer selection

Hello,

 

In this pbix (follow link) there are two pages: page 1 holds 3 tables and one slicer, all populated through the 'fact table' called Planning, and showing the desired behavior. Page 2 holds the exact same content, but with the 'Group' column being replaced by GroupID, which holds the same values but coming from a separate dimension called 'GroupDimension'. In the latter case, all of a sudden the table on the far right does not show the desired behavior anymore, but instead also shows rows with blank values for the status selected in the 'Status' slicer.

 

How do I get the far right table on Page 2 to display the same behavior as on Page 1? I think it is a DAX issue so I tried playing around with ALLEXCEPT, ALLSELECTED and HASONEVALUE in the 'Waiting' and 'Running' measures, but to no avail. HASONEVALUE got me half-way, because it showed the correct thing when a value is selected in the slicer, but then it doesn't show everything I want it to show when nothing/all from the slicer is selected (I don't know why because there is not one value selected then but two, so it shouldn't return anything in my opinion but okay).

 

Thank you very much in advance for any help!

2 ACCEPTED SOLUTIONS
AlB
Super User III
Super User III

Hi @RobinNeven 

Update your measures

 

Running = 
VAR CountRunning = CALCULATE(COUNTROWS(Planning), Planning[Status] = "Running")
RETURN
IF(COUNTROWS(Planning) > 0, CountRunning)
Waiting = 
VAR CountWaiting = CALCULATE(COUNTROWS(Planning), Planning[Status] = "Waiting")
RETURN
IF(COUNTROWS(Planning) > 0, CountWaiting)

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

 

View solution in original post

AlB
Super User III
Super User III

@RobinNeven 

Not sure what you did. I get exactly the same results on page 1 and page 2.

See it all at work in the attached file.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

View solution in original post

4 REPLIES 4
AlB
Super User III
Super User III

@RobinNeven 

Not sure what you did. I get exactly the same results on page 1 and page 2.

See it all at work in the attached file.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

View solution in original post

AlB
Super User III
Super User III

Hi @RobinNeven 

Update your measures

 

Running = 
VAR CountRunning = CALCULATE(COUNTROWS(Planning), Planning[Status] = "Running")
RETURN
IF(COUNTROWS(Planning) > 0, CountRunning)
Waiting = 
VAR CountWaiting = CALCULATE(COUNTROWS(Planning), Planning[Status] = "Waiting")
RETURN
IF(COUNTROWS(Planning) > 0, CountWaiting)

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

 

View solution in original post

RobinNeven
Frequent Visitor

Wow, such a simple solution for something I've been struggling so long with! I was way overthinking it apparently, thanks!

My apologies, but it turns out I was looking at the wrong page (page 1), which lead me to wrongfully conclude that your solution worked. On page 2, the problem still persists with the new measure definitions you provided (the blank value for measure 'Waiting' is still displayed when I select 'Running' in the slicer, and the blank value for measure 'Running' is still displayed when I select 'Waiting' in the slicer, which I don't want).

 

I'm looking forward to any other solutions!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors