Forum Discussion
Measure with user attendance
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.