Measure with user attendance

[jagostinhoCT]

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

[tex628 · Accepted solution]

I think this should work: 

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

[jagostinhoCT]

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]

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

[jagostinhoCT]

Thank you very much for the clarification @tex628 . I am very new to PBI and this is certainly very useful for my learning path.

I think I understand what a Dimension Table is (I went and read about it :)) but it is not clear to me when you say

"This will lead to the ALL statement only removing filters put directly on the sessions column. "?

You mean in the query?

Tried the ALLSELECTED and it works for the general visual, but if I select each name individually in a slicer, the attendance reverts to 100%. This is what you meant by "removing the context filter that the graph applies", I guess.

[tex628]

Hehehehe trying to explain exactly how allselected works is not really something i'm capable of, but im gonna try my best! 🙂 

ALLSELECTED maintains all external filters, any filters from slicers and such. Meaning that if you use a [Name] slicer this will still affect the calculation. The indirect filter thats being applied through the graph gets ignored but any external filters that you apply through slicers will still be active. 

ALL() removes ALL filters from a table. What you generally want to do is pull dimensions out of the main table, so that you can filter/unfilter them without affecting other dimensions. 



My recommendation is that you ignore ALLSELECTED completly and focus on using ALL(). Working with a dataset becomes much easier when you are using seperate dimensiontables. 

What is the source of your dataset? Excel?

[jagostinhoCT]

my brain hurts...LOL

I think I got it, thank you. I will focus on using the ALL() only.

Your dimensional table diagram sure helped too! I am learning a lot today with this "simple" question of mine.

So, basically, I can create a Dimension table by duplicating my initial query and filter out all duplicates. And then link it to my fact table.

My source is a folder with lots of XLS files.

Each XLS is a feedback form created in MS Forms that I download and save it to an online Sharepoint DocLibrary. Then my query looks at all the online XLS.

[tex628]

That would be the best way yes! 

Often when you work with an ERP system as a source there will be dimension tables ready that you can query. But for you when you have excel as a source you should use the initial query and create the tables that you need, just as you said. 

If you need any help, just drop a comment in this thread an ill come and take a look! 🙂 

[jagostinhoCT]

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]

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! 🙂

[jagostinhoCT]

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]

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 🙂

[jagostinhoCT]

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]

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

[jagostinhoCT]

no change after using OneWay

[tex628]

**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

[jagostinhoCT]

Of course.

I should have done it some posts ago. Apologies.

Would it be ok if just copy/pasting

CPD Name	CPD Date	NameShort
Fam	01 February 2018	Macz
Fam	01 February 2018	Idts
Fam	01 February 2018	Lons
Fam	05 February 2018	Raon
Fam	05 February 2018	Toer
Fam	08 February 2018	Dion
Fam	08 February 2018	Stst
Fam	12 February 2018	Toel
Fam	12 February 2018	Jemp
Fam	12 February 2018	Toer
Fam	12 February 2018	Jada
Fam	12 February 2018	Anes
Fam	15 February 2018	Dion
Fam	15 February 2018	Macz
Fam	15 February 2018	Idts
Fam	15 February 2018	Akng
Fam	20 February 2018	Toel
Fam	20 February 2018	Toer
Fam	20 February 2018	Jada
Fam	08 March 2018	Dace
Fam	08 March 2018	Dion
Fam	08 March 2018	Idts
Fam	08 March 2018	Akng
Pro	17 May 2018	Haan
Pro	17 May 2018	Akng
Pro	17 May 2018	Dion
Pro	17 May 2018	Kand
Pro	23 August 2018	Joer
Pro	23 August 2018	Niho
Pro	23 August 2018	Stan
Pro	23 August 2018	Dion
Pro	23 August 2018	Lons
RTV	26 September 2018	Joer
RTV	26 September 2018	Kand
RTV	26 September 2018	Dion
Cla	12 December 2018	Dion
Cla	12 December 2018	Kand
Cla	12 December 2018	Stan
Chr	19 December 2018	Dion
Fam	20 February 2019	Dion
Fam	20 February 2019	Rora
Fam	20 February 2019	Pais
Sta	21 March 2019	Stan
Sta	21 March 2019	Aber
Sta	21 March 2019	Rasu
Sta	21 March 2019	Alni
Sta	21 March 2019	Dion
Sta	18 April 2019	Kand
Sta	18 April 2019	Stan
Sta	18 April 2019	Rasu
Sta	18 April 2019	Daak
Kiw	09 May 2019	Aber
Kiw	09 May 2019	Kand
Kiw	09 May 2019	Rasu
Kiw	09 May 2019	Joer
Fam	13 June 2019	Stan
Fam	13 June 2019	Kand
Fam	13 June 2019	Pais
Fam	27 June 2019	Aber
Fam	27 June 2019	Anan
Fam	27 June 2019	Stan
Fam	27 June 2019	Paam
Ens	11 July 2019	Aber
Ens	11 July 2019	Joey
Ens	11 July 2019	Rasu
Fam	05 February 2018	Manz
Fam	08 February 2018	Anen
Fam	15 February 2018	Algh
Fam	08 March 2018	Alon
Pro	17 May 2018	Maka
Chr	19 December 2018	Saes
Fam	20 February 2019	Maon
Sta	21 March 2019	Kand
Sta	21 March 2019	Niho
Fam	13 June 2019	Kean
Fam	01 February 2018	Kand
Fam	01 February 2018	Algh
Fam	01 February 2018	Alon
Fam	05 February 2018	Jada
Fam	08 February 2018	Pees
Fam	08 February 2018	Joer
Fam	08 February 2018	Alon
Fam	08 February 2018	Maka
Fam	08 February 2018	Kand
Fam	12 February 2018	Chrd
Fam	15 February 2018	Pees
Fam	15 February 2018	Kand
Fam	20 February 2018	Pend
Fam	20 February 2018	Dayo
Fam	08 March 2018	Algh
Pro	17 May 2018	Pees
Cla	12 December 2018	Algh
Cla	12 December 2018	Pees
Cla	12 December 2018	Akng
Cla	12 December 2018	Joer
Fam	20 February 2019	Akng
Fam	20 February 2019	Joer
Sta	21 March 2019	Pees
Sta	21 March 2019	Kean
Sta	21 March 2019	Akng
Sta	18 April 2019	Akng
Fam	13 June 2019	Pees
Fam	13 June 2019	Akng
Fam	27 June 2019	Akng
Fam	27 June 2019	Siey
Ens	11 July 2019	Algh
Fam	05 February 2018	Anli
Fam	08 February 2018	Macz
Fam	12 February 2018	Maka
Fam	08 March 2018	Stan
Pro	17 May 2018	Idts
Pro	17 May 2018	Maon
Pro	17 May 2018	Nenn
Pro	17 May 2018	Stan
Cla	12 December 2018	Aber
Cla	12 December 2018	Mire
Cla	12 December 2018	Joey
Cla	12 December 2018	Rasu
Sta	21 March 2019	Joey
Fam	13 June 2019	Algh
Fam	13 June 2019	Anan
Fam	13 June 2019	Rasu
Fam	27 June 2019	Joey
Ens	11 July 2019	Pais
Ens	11 July 2019	Akng
Fam	01 February 2018	Akng
Fam	01 February 2018	Dion
Fam	01 February 2018	Maka
Fam	05 February 2018	Chrd
Fam	08 February 2018	Stan
Fam	08 February 2018	Nenn
Fam	08 February 2018	Akng
Fam	12 February 2018	Maro
Fam	12 February 2018	Manz
Fam	15 February 2018	Dako
Fam	15 February 2018	Pais
Fam	20 February 2018	Maro
Fam	08 March 2018	Macz
Fam	08 March 2018	Phey
Pro	17 May 2018	Macz
Pro	17 May 2018	Phey
Pro	17 May 2018	Pais
Pro	23 August 2018	Akng
Pro	23 August 2018	Anen
RTV	26 September 2018	Nenn
RTV	26 September 2018	Rasu
RTV	26 September 2018	Aber
Cla	12 December 2018	Phey
Fam	20 February 2019	Pees
Fam	20 February 2019	Rasu
Fam	20 February 2019	Mire
Fam	20 February 2019	Daak
Kiw	09 May 2019	Stan
Kiw	09 May 2019	Rora
Ens	11 July 2019	Alni
Ens	11 July 2019	Zali
Fam	01 February 2018	Joer
Fam	01 February 2018	Stan
Fam	01 February 2018	Nenn
Fam	01 February 2018	Dace
Fam	05 February 2018	Dayo
Fam	05 February 2018	Dans
Fam	05 February 2018	Maro
Fam	08 February 2018	Alni
Fam	08 February 2018	Algh
Fam	08 February 2018	Daod
Fam	12 February 2018	Gino
Fam	12 February 2018	Join
Fam	12 February 2018	Chro
Fam	15 February 2018	Daod
Fam	15 February 2018	Siin
Fam	15 February 2018	Anen
Fam	08 March 2018	Daod
Fam	08 March 2018	Joer
Fam	08 March 2018	Nenn
Fam	08 March 2018	Anen
Pro	17 May 2018	Saes
Pro	17 May 2018	Alni
Pro	17 May 2018	Joer
Pro	17 May 2018	Chod
Pro	23 August 2018	Phey
Pro	23 August 2018	Pais
Pro	23 August 2018	Mire
Pro	23 August 2018	Nenn
Pro	23 August 2018	Raon
Cla	12 December 2018	Nenn
Chr	19 December 2018	Daod
Chr	19 December 2018	Mire
Fam	20 February 2019	Nenn
Fam	20 February 2019	Saes
Fam	20 February 2019	Zali
Sta	21 March 2019	Zali
Sta	21 March 2019	Evsi
Kiw	09 May 2019	Anen
Ens	11 July 2019	Kean
Ens	11 July 2019	Joer
Ens	11 July 2019	Rora
Ens	11 July 2019	Haan

[tex628]

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

[jagostinhoCT]

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

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

[tex628]

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? 

[jagostinhoCT]

Yes! 19 different sessions. That is correct.

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