topic CONVERT SQL SYNTAX TO EQUIVALENT DAX in DAX Commands and Tips

CONVERT SQL SYNTAX TO EQUIVALENT DAX

Jon3sy — Wed, 16 Aug 2023 00:21:20 GMT

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

Re: CONVERT SQL SYNTAX TO EQUIVALENT DAX

some_bih — Wed, 16 Aug 2023 05:22:52 GMT

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
)

Re: CONVERT SQL SYNTAX TO EQUIVALENT DAX

Jon3sy — Wed, 16 Aug 2023 05:37:37 GMT

You're a legend - worked perfect. Immense thanks