Forum Discussion

AlexCassin's avatar
AlexCassin
Frequent Visitor
8 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
    8 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
Icon for Community Support rankCommunity Support
8 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