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

Thanks.

 

So, I have duplicated my single datasource "Feedback" and modelled it so that I only have one row for each CPD session.

 

I have then linked them as below. I used the date because I have different training sessions that have the same name.

 

 

I then edited my Attendance % measure but the staff graph reports everyone w/ an attendance of 100% :-(

Attendance % = DIVIDE(DISTINCTCOUNT('Feedback'[CPD Name]),CALCULATE(DISTINCTCOUNT('Feedback'[CPD Name]),ALL('CPDs'[CPD Name])))
 
I am certainly missing something here...
Is it an obvious error the one I am doing?
tex628's avatar
tex628
Icon for Community Champion rankCommunity Champion
7 years ago

First off all, it's important to observe that you have the relationship on CPD Date but you're counting CPD Name. This means that you disregard sessions with the same name that have occured several times. If you change this to target date instead you will get the % for all individual sessions. 

It's a little hard for me to determine what is wrong currently so do this: 
Make 2 measures:

Measure 1 = distinctcount([CPD Name]) 
Measure 2 = Calculate(distinctcount([CPD Name]) , All(CPDs))

Place those two measures together with [Person] and [Attendance%] in a normal table and take a a picture. 

Also, remove the ALL('CPDs'[Name]) from the all statment! :)

  • tex628's avatar
    tex628
    Icon for Community Champion rankCommunity Champion
    7 years ago

    **bleep**! 
    Well my head is hurting abit aswell now, any chance you could send me a sample version of the file? Writing code in my head is just not cutting it anymore

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Here is a snapshot.

     

    Each row represents one person.

     

    My measures as currently as follow:

     

     

    Attendance % = DIVIDE(DISTINCTCOUNT('Feedback'[CPD Name]),CALCULATE(DISTINCTCOUNT('Feedback'[CPD Name]),ALL('CPDs')))
     
    Changed these to count the Date, as you noted that it is actually what I want to count (since different dates have the same name).
    Measure 1 = DISTINCTCOUNT(CPDs[CPD Date])
    Measure 2 = CALCULATE(DISTINCTCOUNT(CPDs[CPD Date]),ALL(CPDs))
     
    Many thanks
    J

  • tex628's avatar
    tex628
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Replace measure 2 with: 

    Measure 2 = 
CALCULATE(
DISTINCTCOUNT('CPDs'[CPD Date]),CROSSFILTER('Feedback'[CPD Date],'CPDs'[CPD Date],None) , ALL('CPDs'))

    The doublesided crossfilter is what's messing it up so we have to remove it. Just why it's doing that is beyond me :-) Theres alot to learn still :)

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Adding that change to Measure 2 gets all CPDs regardless of the report slicer being applied.

     

    I thought about creating a

     

    Measure 3 = DISTINCTCOUNT(Feedback[Name])/CALCULATE(DISTINCTCOUNT(CPDs[CPD Date]),ALL(CPDs))
    and an
    Attendance 2 = [Measure 1]/[Measure 2]
     
    Added those to the table and created a matrix where one could quickly check how many CPD dates each person attended (NameShort). Also created another table with the CPD Dates
     
    So, by looking at the NameShort matrix and the CPD Date table, "Aber" attended 2 out of 33 sessions, so the attendance should be 0.66 but neither of the measures is doing that.
     
    Does this make sense?
  • tex628's avatar
    tex628
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Swap the last part of the CROSSFITLER() statement, from None to OneWay. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    no change after using OneWay

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Of course.

     

    I should have done it some posts ago. Apologies.

     

    Would it be ok if just copy/pasting

     

    CPD NameCPD DateNameShort
    Fam01 February 2018Macz
    Fam01 February 2018Idts
    Fam01 February 2018Lons
    Fam05 February 2018Raon
    Fam05 February 2018Toer
    Fam08 February 2018Dion
    Fam08 February 2018Stst
    Fam12 February 2018Toel
    Fam12 February 2018Jemp
    Fam12 February 2018Toer
    Fam12 February 2018Jada
    Fam12 February 2018Anes
    Fam15 February 2018Dion
    Fam15 February 2018Macz
    Fam15 February 2018Idts
    Fam15 February 2018Akng
    Fam20 February 2018Toel
    Fam20 February 2018Toer
    Fam20 February 2018Jada
    Fam08 March 2018Dace
    Fam08 March 2018Dion
    Fam08 March 2018Idts
    Fam08 March 2018Akng
    Pro17 May 2018Haan
    Pro17 May 2018Akng
    Pro17 May 2018Dion
    Pro17 May 2018Kand
    Pro23 August 2018Joer
    Pro23 August 2018Niho
    Pro23 August 2018Stan
    Pro23 August 2018Dion
    Pro23 August 2018Lons
    RTV26 September 2018Joer
    RTV26 September 2018Kand
    RTV26 September 2018Dion
    Cla12 December 2018Dion
    Cla12 December 2018Kand
    Cla12 December 2018Stan
    Chr19 December 2018Dion
    Fam20 February 2019Dion
    Fam20 February 2019Rora
    Fam20 February 2019Pais
    Sta21 March 2019Stan
    Sta21 March 2019Aber
    Sta21 March 2019Rasu
    Sta21 March 2019Alni
    Sta21 March 2019Dion
    Sta18 April 2019Kand
    Sta18 April 2019Stan
    Sta18 April 2019Rasu
    Sta18 April 2019Daak
    Kiw09 May 2019Aber
    Kiw09 May 2019Kand
    Kiw09 May 2019Rasu
    Kiw09 May 2019Joer
    Fam13 June 2019Stan
    Fam13 June 2019Kand
    Fam13 June 2019Pais
    Fam27 June 2019Aber
    Fam27 June 2019Anan
    Fam27 June 2019Stan
    Fam27 June 2019Paam
    Ens11 July 2019Aber
    Ens11 July 2019Joey
    Ens11 July 2019Rasu
    Fam05 February 2018Manz
    Fam08 February 2018Anen
    Fam15 February 2018Algh
    Fam08 March 2018Alon
    Pro17 May 2018Maka
    Chr19 December 2018Saes
    Fam20 February 2019Maon
    Sta21 March 2019Kand
    Sta21 March 2019Niho
    Fam13 June 2019Kean
    Fam01 February 2018Kand
    Fam01 February 2018Algh
    Fam01 February 2018Alon
    Fam05 February 2018Jada
    Fam08 February 2018Pees
    Fam08 February 2018Joer
    Fam08 February 2018Alon
    Fam08 February 2018Maka
    Fam08 February 2018Kand
    Fam12 February 2018Chrd
    Fam15 February 2018Pees
    Fam15 February 2018Kand
    Fam20 February 2018Pend
    Fam20 February 2018Dayo
    Fam08 March 2018Algh
    Pro17 May 2018Pees
    Cla12 December 2018Algh
    Cla12 December 2018Pees
    Cla12 December 2018Akng
    Cla12 December 2018Joer
    Fam20 February 2019Akng
    Fam20 February 2019Joer
    Sta21 March 2019Pees
    Sta21 March 2019Kean
    Sta21 March 2019Akng
    Sta18 April 2019Akng
    Fam13 June 2019Pees
    Fam13 June 2019Akng
    Fam27 June 2019Akng
    Fam27 June 2019Siey
    Ens11 July 2019Algh
    Fam05 February 2018Anli
    Fam08 February 2018Macz
    Fam12 February 2018Maka
    Fam08 March 2018Stan
    Pro17 May 2018Idts
    Pro17 May 2018Maon
    Pro17 May 2018Nenn
    Pro17 May 2018Stan
    Cla12 December 2018Aber
    Cla12 December 2018Mire
    Cla12 December 2018Joey
    Cla12 December 2018Rasu
    Sta21 March 2019Joey
    Fam13 June 2019Algh
    Fam13 June 2019Anan
    Fam13 June 2019Rasu
    Fam27 June 2019Joey
    Ens11 July 2019Pais
    Ens11 July 2019Akng
    Fam01 February 2018Akng
    Fam01 February 2018Dion
    Fam01 February 2018Maka
    Fam05 February 2018Chrd
    Fam08 February 2018Stan
    Fam08 February 2018Nenn
    Fam08 February 2018Akng
    Fam12 February 2018Maro
    Fam12 February 2018Manz
    Fam15 February 2018Dako
    Fam15 February 2018Pais
    Fam20 February 2018Maro
    Fam08 March 2018Macz
    Fam08 March 2018Phey
    Pro17 May 2018Macz
    Pro17 May 2018Phey
    Pro17 May 2018Pais
    Pro23 August 2018Akng
    Pro23 August 2018Anen
    RTV26 September 2018Nenn
    RTV26 September 2018Rasu
    RTV26 September 2018Aber
    Cla12 December 2018Phey
    Fam20 February 2019Pees
    Fam20 February 2019Rasu
    Fam20 February 2019Mire
    Fam20 February 2019Daak
    Kiw09 May 2019Stan
    Kiw09 May 2019Rora
    Ens11 July 2019Alni
    Ens11 July 2019Zali
    Fam01 February 2018Joer
    Fam01 February 2018Stan
    Fam01 February 2018Nenn
    Fam01 February 2018Dace
    Fam05 February 2018Dayo
    Fam05 February 2018Dans
    Fam05 February 2018Maro
    Fam08 February 2018Alni
    Fam08 February 2018Algh
    Fam08 February 2018Daod
    Fam12 February 2018Gino
    Fam12 February 2018Join
    Fam12 February 2018Chro
    Fam15 February 2018Daod
    Fam15 February 2018Siin
    Fam15 February 2018Anen
    Fam08 March 2018Daod
    Fam08 March 2018Joer
    Fam08 March 2018Nenn
    Fam08 March 2018Anen
    Pro17 May 2018Saes
    Pro17 May 2018Alni
    Pro17 May 2018Joer
    Pro17 May 2018Chod
    Pro23 August 2018Phey
    Pro23 August 2018Pais
    Pro23 August 2018Mire
    Pro23 August 2018Nenn
    Pro23 August 2018Raon
    Cla12 December 2018Nenn
    Chr19 December 2018Daod
    Chr19 December 2018Mire
    Fam20 February 2019Nenn
    Fam20 February 2019Saes
    Fam20 February 2019Zali
    Sta21 March 2019Zali
    Sta21 March 2019Evsi
    Kiw09 May 2019Anen
    Ens11 July 2019Kean
    Ens11 July 2019Joer
    Ens11 July 2019Rora
    Ens11 July 2019Haan
  • tex628's avatar
    tex628
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Just have to check, you wrote 33 sessions previously but i can only find 19 unique session. Did i miss something? 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Not sure what to answer. Measure 1 is counting the number of sessions. It reads 16.

    Why would Measure 2 count 19...? :-s

  • tex628's avatar
    tex628
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Lets take the logic step by step!  


    Each unique date in the sample data represents a unique session. In the dataset there are a total of 19 different dates. This must mean that there are a total of 19 potential sessions that each user can go to, right? 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Yes! 19 different sessions. That is correct.

    I was counting 16 because I had another slicer applied. My mistake.