derived column based on nested case statements across multiple columns from multiple dim tables

[Jeni2608]

how to convert the below SQL in Power BI report, this is the report level derived column .

(CASE WHEN Dim1.col1 like '%OT%' THEN (case when Dim2.col2 = 'NOT APPLICABLE' OR Dim2.col3 IS NULL THEN Dim3.col1 ELSE Dim2.col2 END) ELSE (Dim1.col1 IS NULL OR Dim3.col1 = 'NOT APPLICABLE' THEN Dim2.col2 ELSE Dim3.col1 END) END)

[VahidDM]

Hi @Jeni2608 

It would be better if you share more info about your model and relations between tables, BTW, 

In Power BI, you can create a calculated column using the DAX formula to achieve similar result of the above SQL code. The DAX formula would be:

IF( CONTAINS(Dim1[col1], "OT"), IF( OR(Dim2[col2] = "NOT APPLICABLE", ISBLANK(Dim2[col3])), Dim3[col1], Dim2[col2] ), IF( OR(ISBLANK(Dim1[col1]), Dim3[col1] = "NOT APPLICABLE"), Dim2[col2], Dim3[col1] ) )

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[Jeni2608]

Thanks for the quick reply, i will share the model soon, BTW - on which table should i create this calculated column out of the three dimension tables involved here.