SQL Case Statement to Dax

[llm]

Hi In the instructions it says not to ask about SQL conversions but I'm willing to take a chance that someone can help . I am a beginner with Power Bi & Dax and having difficulty converting the following SQL case statement to Dax. All the appropriate relationships have been created. How do I recreate the nested statements and figure out how to enter 0 if the conditions are not met? Do I use a switch or calculate with variables?

Statement overview: Create a new column called "CurrRetain" . Populate it with APTD.Amount (if conditions are met) or 0 (if they are not).

WHEN APTD.PayType = (

                                      CASE

                                              WHEN APTD.PayCategory IS NULL

                                                      THEN APCO.RetPayType

                                              ELSE APPC.RetPayType

                                              END

                                      )

                               AND (

                                      APTD.PaidDate IS NULL

                                      OR APTD.PaidDate > GETDATE()

                                      )

                               AND APTH.InvDate <= GETDATE()

                               THEN (APTD.Amount)

                       ELSE 0

                       END AS 'CurrRetain'

Any help or suggestion in converting this to dax would be greatly appreciated.

[v-luwang-msft]

Hi @llm ,

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.If not solved ,could you pls share your sample data in SQL,and expected output.

Remember to remove confidential data.

Best Regards
Lucien

[bcdobbs · Accepted solution]

I would look at using SWITCH but in the form

SWITCH (

   True,

   Case1, Outcome1,

    Case2, Outcome2

   ....

)

See the example in https://dax.guide/switch/

[llm]

This worked

CurrRetain = SWITCH(TRUE,

IF (ISBLANK(APTD[PayCategory]),RELATED(APCO[RetPayType]),RELATED(APPC[RetPayType])) &&

(ISBLANK(APTD[PaidDate])) || ((APTD[PaidDate]) > TODAY()) &&

(RELATED(APTH[InvDate]) <= TODAY()),

APTD[Amount],

0

)