Getting Date Range Slicer to Play Nice with Measure

[Anonymous]

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. 

[v-xjiin-msft]

Hi @Anonymous,

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.

[Anonymous]

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:

[waltheed · Accepted solution]

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]

Hope this helps. 

[Anonymous]

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.