Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Measures and Filters

Currently, I have my data set to 3 shifts; A, B and C. I need to get a card to show the total number of days where there was more than 20 occurrences of High in an hour. I'm at a complete loss on how...
Need Help
Show and Tell
Tutorial Requests
  • rsbin's avatar
    rsbin
    2 years ago

    Anonymous ,

    Copied your data into my pbix as Sheet1. 
    Then re-did all columns and the Measure.  Think I may have had to edit one of my calculated columns.

    Result now comes out as 2.
    Please review and hopefully this works for you.

    Regards,

rsbin's avatar
rsbin
Community Champion
2 years ago

Anonymous ,

Please review the attached pbix file.

Solution involves creating several calculated columns and then finally a measure to Count Days > 20.

Hope you can follow through on the logic I used.  May need some tweaks depending on the remainder of your data set.

Good Luck and Regards,

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thanks for the response. Strangely after copying your columns and changing to point at my table etc, it brings back '31' which seems to be day count, rather than times days where there has been 20 or more High Category

    • rsbin's avatar
      rsbin
      Community Champion
      2 years ago

      Anonymous ,

      Are the Columns calculating correctly?

      Please show me the Measure you are using or better yet, can you attach your PBIX file?

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Doesnt seem to be giving me any errors, and everything is just copied from yours with the references changed to reference my Table. 
        I can't share the PBIX file unfortunately as there's some sensitive info, however I've uploaded the data sample for the full month (Category & Time) - https://we.tl/t-7MSYp7mGCD 

        Also below is the calculations I've used:

        - Category_Count = CALCULATE( COUNTA( 'Main Data'[Category_Count ] ),
                               ALLEXCEPT( 'Main Data', 'Main Data'[Hours], 'Main Data'[Category_Count ] ))


        - DateOnly = FORMAT( [Time], "mm/dd/yyyy" )

        -         Hours = Hour([Time])

        -         DaysCount_High > 20 = CALCULATE( DISTINCTCOUNT( [DateOnly] ),

                                       FILTER( 'Main Data', 'Main Data'[Category] = "High" &&
                                               'Main Data'[Category_Count] > 20 )) + 0