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

Getting Date Range Slicer to Play Nice with Measure

So, I'm working with a measure that takes a column with TRUE/FALSE boolean values (to track whether or not an issue was resolved), and provides the % of "TRUE" - this is accomplished using the following syntax:

DIVIDE(COUNTA(Q1[Resolved]),CALCULATE(COUNTA(Q1[Resolved]),ALL(Q1[Resolved])))*100

Which then has a basic visual level filter applied to it for when [Resolved] is TRUE. This gives me an accurate percentage.

 

However, if I add a slicer based on a date (in this case, a [CompletedDate] column, which is formatted MM/DD/YYYY HH:MM:SS AM) and start filtering, the percentage breaks entirely, typically displaying as "100." I assume there's something in the measure that makes slicers break it, but I'm having trouble figuring where I should start with it. 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi , 

 

I changed the DAX formula a bit. This should work:

 

RESOLVEDPERCENT = DIVIDE(calculate(COUNTROWS(Table1); Table1[RESOLVED] = true); COUNTROWS(Table1))

NOT RESOLVEDPERCENT = 1 -[RESOLVEDPERCENT]

 

Capture.PNG

 

Hope this helps. 

 

 

Cheers, Edgar Walther
ITsmart BI and Analytics consultant
Power BI User Group Netherlands

View solution in original post

4 REPLIES 4
Solution Sage
Solution Sage

Hi @diagnosed,

 

First ALL() function will ignore any filters that might have been applied. Please consider this.

 

Then with current information, it is hard us for to find out where the issue is. So please share us more detailed information like some sample data which we can copy and paste directly and its corresponding expected result.

 

Also, if you can share us your pbix file with One Drive or Google Drive. It'll help us more to get a right direction.

 

Thanks,
Xi Jin.

I've uploaded an example .pbix I've put togethere here - it only has five rows and three columns, but it illustrates exactly the problem I'm running into.

 

I think that the problem might be, as per what you said, that the measure doesn't care about any filtering that's applied - which I'm assuming might be why it returns 100% even when the count of items shows otherwise?

 

I also snipped the following examples of the problem in action to help with reproducing it in the example .pbix:

 

Demo 1.PNGDemo 2.PNG

Hi , 

 

I changed the DAX formula a bit. This should work:

 

RESOLVEDPERCENT = DIVIDE(calculate(COUNTROWS(Table1); Table1[RESOLVED] = true); COUNTROWS(Table1))

NOT RESOLVEDPERCENT = 1 -[RESOLVEDPERCENT]

 

Capture.PNG

 

Hope this helps. 

 

 

Cheers, Edgar Walther
ITsmart BI and Analytics consultant
Power BI User Group Netherlands

View solution in original post

Thank you! This is fantastic, and I can see what you did with the formula which should help me going forward to. I applied it to my actual dataset and it works flawlessly, this is sincerely appreciated.

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors