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.
Hi all
I am hoping someone can help with this measure, I have tried DISTINCTCOUNT with this in lots of ways but I cant seem to get what I need. I have the following two tables:
Table - Reports
ID LiftID
1 122
2 256
3 122
4 665
5 665
6 788
7 788
8 788
9 256
Table - Lifts
LiftID EngID
122 CR01
256 NA01
665 CR01
788 ST02
687 NA01
688 NA01
I want to be able to count how many reports each engineer EngID has, and divide by a count of how many lifts each engineer (EngID) has. There are multiple filters on the screen too, so any filters applied would also be taken into account when calculating the counts and the measure
So for the above tables, I have the following counts:
Reports
EngID Count
CR01 4
NA01 2
ST02 3
Lifts
EngID Count
CR01 2
NA01 3
ST02 1
And the measure should return:
Reports.EngCount / Lifts.EngCount
CR01 2 (this is calculated as 4 / 2)
NA01 0.666 (this is calculated as 2 / 3)
ST02 3 (this is calculated as 3 / 1)
I just cannot figure out how to do this at all, so any help would be most appreciated
Cheers
Solved! Go to Solution.
Hello @ansa_naz,
Please use the following measure:
Measure = DIVIDE(COUNT(Reports[LiftID]),COUNT(Lifts[LiftID]))
Hope this helps.
Hi @ansa_naz
You can see the attached PBIX file here .
I have got the following result by addeing the measures.
Required = Var countR =CALCULATE(COUNTROWS(Reports),ALLEXCEPT(Lifts,Lifts[EngID])) Var countl=CALCULATE(COUNTROWS(Lifts),ALLEXCEPT(Lifts,Lifts[EngID])) Return countR/countl
If this helped you, please mark this post as an accepted solution and like to give KUDOS .
Regards,
Affan
Hello @ansa_naz,
Please use the following measure:
Measure = DIVIDE(COUNT(Reports[LiftID]),COUNT(Lifts[LiftID]))
Hope this helps.
Hi @ansa_naz
Create Relationship between these two tables and write the below DAX to create a measure.
TotalCount = SUM(Reports[Count])/SUM(Lifts[Count])
For more details see the pbix file.
Hi @Anonymous this doesnt work as the column 'Count' does not exist. I am trying to write a DAX measure which uses Count within it, it is not already an existing column
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |