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.
Hello,
I have two datasets as follows:
Dataset 1
ID | Erect Hours | Dismantle Hours |
1 | 10 | 20 |
2 | 5 | 10 |
Dataset 2
ID | Job Type | % Complete | Burned |
1 | Erect | 100% | 12 |
2 | Erect | 100% | 5 |
2 | Dismantle | 100% | 10 |
2 | Day Work Mod | 5 | |
2 | Star Rate Mod | 100% | 4.5 |
Relationship has been created between ID in both datasets.
I need to create a DAX measure as follows:
If job type is 'Erect' then (% complete * erect hours)
If job type is 'Dismantle' then (% complete * dismantle hours)
If job type is 'Star Rate Mod' then (% complete * 4.5)
If job type is 'Day Work Mod' then (sum burned)
Is the above logic possible in one DAX statement?
Solved! Go to Solution.
@Serdet , A new column in dataset2
new column =
var _1 = sumx(filter(dataset1, dataset1[ID] = dataset2[ID]) ,[Erect Hours])
var _2 = sumx(filter(dataset1, dataset1[ID] = dataset2[ID]) ,[Dismantle Hours])
return
Switch([Job Type],
"Erect", [% complete]*_1,
"Dismantle", [% complete]*_2,
"Star Rate Mod", [% complete]*.45,
"Dismantle", [Burned]
)
@Serdet , A new column in dataset2
new column =
var _1 = sumx(filter(dataset1, dataset1[ID] = dataset2[ID]) ,[Erect Hours])
var _2 = sumx(filter(dataset1, dataset1[ID] = dataset2[ID]) ,[Dismantle Hours])
return
Switch([Job Type],
"Erect", [% complete]*_1,
"Dismantle", [% complete]*_2,
"Star Rate Mod", [% complete]*.45,
"Dismantle", [Burned]
)
Thanks for answering my questions.
Is there anyway I can use the below formular but reference columns from another table in the highlighted section of your code?
new column =
var _1 = sumx(filter(dataset1, dataset1[ID] = dataset2[ID]) ,[Erect Hours])
var _2 = sumx(filter(dataset1, dataset1[ID] = dataset2[ID]) ,[Dismantle Hours])
These highlighted columns come from another table that is connect via a many to many relationship.
Many thanks,
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 |
---|---|
115 | |
99 | |
88 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |