Multiple IF Calculation

[Anonymous]

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?

[amitchandak · Accepted solution]

@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]

)

[Anonymous]

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,