Forum Discussion
Multiple IF Calculation
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?
Anonymous , 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]
)
2 Replies
Anonymous , 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,