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 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.
Community ChampionHehehehe 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.
- 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! :)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?