Forum Discussion

llm's avatar
llm
Frequent Visitor
4 years ago
Solved

SQL Case Statement to Dax

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.

 

Need Help

3 Replies

  • bcdobbs's avatar
    bcdobbs
    Community Champion
    4 years ago

    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's avatar
      llm
      Frequent Visitor
      4 years ago

      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

      )

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    4 years ago

    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