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,...
  • 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

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!