Forum Discussion

Jon3sy's avatar
Jon3sy
Icon for Helper I rankHelper I
3 years ago
Solved

CONVERT 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 THE...
  • some_bih's avatar
    some_bih
    3 years ago

    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
    )

some_bih's avatar
some_bih
Icon for Community Champion rankCommunity Champion
3 years ago

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's avatar
Jon3sy
Icon for Helper I rankHelper I
3 years ago

You're a legend - worked perfect. Immense thanks