Forum Discussion
derived column based on nested case statements across multiple columns from multiple dim tables
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)
2 Replies
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!!
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.
