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.
I have data in 2 tables as below. The tables are not related in BI Desktop. How do I write a formula in DAX to achieve this?
Table 1
Role Total Mission Hours By Region Name
ATTORNEY 22701
ATTORNEY III 20823
ATTORNEY IV 15809
Attorney V 11575
Table 2
Labor Class Name Total Current Hours
Attorney III 16356
Attorney 15200
Attorney IV 11910
Assistant Chief Counsel 4768
Output Requires is -
Labor Class Name Total Current Hours Total Mission Hours
Attorney III 16356 20823
Attorney 15200 22701
Attorney IV 11910 15809
Assistant Chief Counsel 4768
Solved! Go to Solution.
Well, you have to decide what you want to do. I can't tell you, it's your business after all 🙂
You could sum up all the values in the corresponding rows or you could take the min/max... you could do many things but it's you who has to decide. You could also return BLANK if there is more than 1 corresponding row. It's your call, mate 🙂
Best
Darek
The code
-- This code will return an error if
-- there is more than 1 row in T1
-- for any of the roles. But according
-- to the conditions there should be
-- only unique roles in T1.
[Total Mission Hours] = -- calculated column
var __role = T2[Labor Class Name]
var __totalMissionHours =
CALCULATE (
VALUES ( T1[Total Mission Hours By Region Name] ),
T1[Role] = __role
)
return
__totalMissionHours
Best
D.
Hi,
Thanks for replying!
I gave an overview of what I am trying to achieve. there is more than 1 row in T1.
What can be done if this is the scenario?
Thanks,
Well, you have to decide what you want to do. I can't tell you, it's your business after all 🙂
You could sum up all the values in the corresponding rows or you could take the min/max... you could do many things but it's you who has to decide. You could also return BLANK if there is more than 1 corresponding row. It's your call, mate 🙂
Best
Darek
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 |
---|---|
43 | |
21 | |
20 | |
15 | |
13 |
User | Count |
---|---|
45 | |
41 | |
39 | |
19 | |
19 |