Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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. 

Need Help
  • waltheed's avatar
    waltheed
    8 years ago

    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. 

     

     

4 Replies

  • v-xjiin-msft's avatar
    v-xjiin-msft
    Icon for Solution Sage rankSolution Sage
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        waltheed
        Icon for Impactful Individual rankImpactful Individual
        8 years ago

        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.