Forum Discussion

AlexCassin's avatar
AlexCassin
Frequent Visitor
7 years ago
Solved

SQL CASE Statement to DAX

I have this statement that uses a range of possible data so I dont believe i can just use a switch and im having major issues getting my head around it.

 

CASE WHEN JobLoggedDate > DATEADD(day, -7, GETDATE()) THEN '< 1 Week' WHEN JobLoggedDate <= DATEADD(day, -7, GETDATE()) AND JobLoggedDate > DATEADD(month, -1, GETDATE()) THEN '1 Week - 1 Month' WHEN JobLoggedDate <= DATEADD(month, -1, GETDATE()) AND JobLoggedDate > DATEADD(month, -3, GETDATE()) THEN '1 Month - 3 Months' ELSE '> 3 Months' END AS 'AgeRange'

 How do i translate this to DAX so i can set a new column in a set of returned data to use this logic?

  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi AlexCassin

    Try this formula to create a calculated column

    'AgeRange' =
SWITCH (
    TRUE (),
    [JobLoggedDate]
        > TODAY () - 7, "< 1 Week",
    [JobLoggedDate]
        <= TODAY () - 7
        && [JobLoggedDate]
            > DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, DAY ( TODAY () ) ), "1 Week - 1 Month",
    [JobLoggedDate]
        <= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, DAY ( TODAY () ) )
        && [JobLoggedDate]
            > DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 3, DAY ( TODAY () ) ), "1 Month - 3 Months",
    "1 Month - 3 Months"
)

    Here is my test pbix

     

    Best Regards

    Maggie

2 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi AlexCassin

    Try this formula to create a calculated column

    'AgeRange' =
SWITCH (
    TRUE (),
    [JobLoggedDate]
        > TODAY () - 7, "< 1 Week",
    [JobLoggedDate]
        <= TODAY () - 7
        && [JobLoggedDate]
            > DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, DAY ( TODAY () ) ), "1 Week - 1 Month",
    [JobLoggedDate]
        <= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, DAY ( TODAY () ) )
        && [JobLoggedDate]
            > DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 3, DAY ( TODAY () ) ), "1 Month - 3 Months",
    "1 Month - 3 Months"
)

    Here is my test pbix

     

    Best Regards

    Maggie

    • AlexCassin's avatar
      AlexCassin
      Frequent Visitor
      7 years ago

      Worked like a charm! Except 1 issue, you had put down the default to "1 Month - 3 Months" instead of the "> 3 Months" I had but only a small issue :) Just thought id let you know!