Forum Discussion
Helper ICONVERT SQL SYNTAX TO EQUIVALENT DAX
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
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
)
2 Replies
- some_bih
Community Champion
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
)
Jon3syYou're a legend - worked perfect. Immense thanks