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

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's avatar
tex628
Icon for Community Champion rankCommunity Champion
7 years ago

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! :)