SQL to DAX

[Ghazaleh]

How do I convert this from SQL to DAX format?

CASE

    WHEN dp.payment_status IN ('Approved', 'PaidInFull') THEN 0

    ELSE fp.revenue_amount_{{currency}}

END AS booking_amount,

CASE

    WHEN dp.payment_status IN ('Approved', 'PaidInFull') THEN fp.revenue_amount_{{currency}}

    ELSE 0

END AS payment_amount,

[AllisonKennedy]

@Ghazaleh What's the FROM clause of your SQL statement and how are the fp and dp tables related in Power BI?

A CASE statement is basically a SWITCH function in DAX or a Conditional Column in Power Query. 

Use

Booking_amount = 

SWITCH (  TRUE(), 

dp.payment_status IN {'Approved', 'PaidInFull'} , 0, 

fp.revenue_amount_{{currency}}

)

But you'll need to clean up the syntax and table names.

[Ghazaleh]

the payment status comes from a different table (dp) and revenue amount comes from (fp). the approved and paidinfull are values in payment status columns. How can I use "related" function in this case statement to pull these variables from both tables?

[v-jingzhang · Accepted solution]

Hi @Ghazaleh 

The RELATED function requires that a relationship exists between the current table and the table with related information. If a relationship does not exist between "fp" and "dp", you must create a relationship. You can create a relationship on their common column e.g. Order ID, Payment ID...

Then if you want to add two columns in "fp", you can try the following DAX. You might need to correct the table and column names. 

booking_amount =
IF (
    RELATED ( 'dp'[payment_status] ) IN { "Approved", "PaidInFull" },
    0,
    'fp'[revenue_amount_]
)

payment_amount =
IF (
    RELATED ( 'dp'[payment_status] ) IN { "Approved", "PaidInFull" },
    'fp'[revenue_amount_],
    0
)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.