CONVERT SQL SYNTAX TO EQUIVALENT DAX

[Jon3sy]

I am wondering how to convert this specific sql syntax to an "equivalent" DAX syntax if it is possible? Thanks in advance.

Customer Level CPI =
(CASE WHEN SUM(CASE WHEN ISNULL(amount_sell,0)>0
THEN 1
ELSE 0
END) > 0 THEN
ROUND (SUM(CASE WHEN ISNULL(amount_sell,0)>0
THEN CONVERT(NUMERIC(10,2),((ISNULL(initial_budget,0) + ISNULL(approved_changes,0)) * (ISNULL(progress_pct,0) / 100)) / amount_sell)
ELSE 0
END)/SUM(CASE WHEN ISNUL(amount_sell,0)>0
THEN 1
ELSE 0
END),2) ELSE 0 END) as CPI

[some_bih · Accepted solution]

Hi @Jon3sy  using AI proposed solution is as following:

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Customer Level CPI =
IF (
SUMX (
FILTER (
YourTableName,
NOT ISBLANK ( [amount_sell] ) && [amount_sell] > 0
),
1
) > 0,
ROUND (
SUMX (
FILTER (
YourTableName,
NOT ISBLANK ( [amount_sell] ) && [amount_sell] > 0
),
(
( [initial_budget] + [approved_changes] )
* [progress_pct] / 100
) / [amount_sell]
)
/ SUMX (
FILTER (
YourTableName,
NOT ISBLANK ( [amount_sell] ) && [amount_sell] > 0
),
1
),
2
),
0
)

[Jon3sy]

You're a legend - worked perfect. Immense thanks