Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
I think this should work:
Measure = DIVIDE(DISTINCTCOUNT('Sample'[Session]) , CALCULATE(DISTINCTCOUNT('Sample'[Session]) , ALL('Sample')) , BLANK())
I think this should work:
Measure = DIVIDE(DISTINCTCOUNT('Sample'[Session]) , CALCULATE(DISTINCTCOUNT('Sample'[Session]) , ALL('Sample')) , BLANK())
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
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
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.
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?
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.
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! 🙂
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% 😞
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! 🙂
Here is a snapshot.
Each row represents one person.
My measures as currently as follow:
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 🙂
Adding that change to Measure 2 gets all CPDs regardless of the report slicer being applied.
I thought about creating a
Swap the last part of the CROSSFITLER() statement, from None to OneWay.
no change after using OneWay
**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
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 |
Just have to check, you wrote 33 sessions previously but i can only find 19 unique session. Did i miss something?
Not sure what to answer. Measure 1 is counting the number of sessions. It reads 16.
Why would Measure 2 count 19...? :-s
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?
Yes! 19 different sessions. That is correct.
I was counting 16 because I had another slicer applied. My mistake.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |