Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Measure with user attendance

Hello,

 

I would like to calculate staff attendance to training sessions.

 

See the sample table below.

I guess I am getting all to show 100% because the DISTINCTCOUNT is being filtered by user too.

How can I make it to lock to the full Session distinct count instead?

 

According to the sample it should be

Ann = 100%

Joe = 50%

Mark = 25%

 

 

Thank you

Need Help
  • tex628's avatar
    tex628
    7 years ago

    I think this should work: 

    Measure = 
DIVIDE(DISTINCTCOUNT('Sample'[Session]) , CALCULATE(DISTINCTCOUNT('Sample'[Session]) , ALL('Sample')) , BLANK())

20 Replies

  • tex628's avatar
    tex628
    Community Champion
    7 years ago

    I think this should work: 

    Measure = 
DIVIDE(DISTINCTCOUNT('Sample'[Session]) , CALCULATE(DISTINCTCOUNT('Sample'[Session]) , ALL('Sample')) , BLANK())
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you tex628 . It works a treat. I needed to remove the BLANK() at the end, though, as it was giving me an error when using it.

       

      I am not entirely clear on what the formula is doing?

      I understand that you are dividing the filtered DISTINCTCOUNT of session by the unfiltered one. But why do you need to use the CALCULATE for that?

       

      And another question, in my real datasource I have much more sessions with dates. Will this formula work if I use a date slicer, since it is using the ALL to remove all filters?

       

      Thank you once again

      J

      • tex628's avatar
        tex628
        Community Champion
        7 years ago

        When you have [Name] as the axis, what happens is that the expression is evaluated one time for each distinct value that can be found in the [Name] column.

        This means that there's essentailly a [Name] filter put on the expression automatically which results in the first distinctcount calculating only the number of sessions for that specific name.

        The calculate statment is for the the second distinctcount. As i need to apply an ALL statement that removes the overlaying filter. This results in you getting the total amount of sessions in the entire dataset. 

        But your concern is legitimate as the all statement will remove ALL filters meaning that if you slice the data you will still get sessions outside of the daterange. 

        This can be solved in a few different way. Personally i would use a dimension table for the sessions, which i would then use the ALL statement on. This will lead to the ALL statement only removing filters put directly on the sessions column. 

        Another solution would be swapping the ALL statement for an ALLSELECTED statement. This should allow slicer filtes to still be considered, but remove the context filter that the graph applies.

        I hope this gave you some insight! 

        / J