Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 ...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • amitchandak's avatar
    amitchandak
    3 years ago

    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]

    )

     

amitchandak's avatar
amitchandak
Super User
3 years ago

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's avatar
    Anonymous
    Not applicable
    3 years ago

    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,