Obtuse SQL Case statement to DAX

[AliH]

Hi All,

First post here - DAX is defeating me on this one. I know the basic SWITCH(), but I have a more complicated 'Switch' I'm trying to get working in DAX and hoping some guru here can help.

My case statement is as follows, and tracks what overall 'status' the business defines an order as.

Not copied exact SQL, just written the outline and a few exampes of the somewhat obtuse case, ther are some 15+ in total

CASE

    WHEN Status = T  AND (ShippingDate <= CURDATE() OR complete = 0) THEN "Trapping"

    WHEN Status = T AND (ShippingDate > CURDATE() OR Complete <> 0) THEN "FutureTrapping"
    WHEN Status = O and OrderType = Web THEN WebOpen
    WHEN Status = O AND NOT OrderType = Web THEN NonWebOpen

    WHEN Despatched = 0 AND Date(ShipDate) > DATE(NOW()) THEN "Future"

    WHEN (((Status IN ("P","H")) OR (((Status != X) AND Stopped = -1) OR (Packed = -1 AND complete = 0)) And Status <> T Then "Stopped/Parked"

And a bunch of others.

Eseentially I am trying to get a switch statement to work on a few columns, I think I might need Nested switches. But as I said, this is currently defeating me.

Any help is much appreciated.

Many thanks,

Ali H

[Anonymous]

Hey @AliH

Are you connecting to a SQL datasource? If so, why not just leave your case statement in SQL and bring it in that way?

Thanks,

Parker

[AliH]

Hi Parker,

I should have perhaps mentioned. The business' main database is MySQL - I pull the data in to an MS SQL Warehouse with SSIS, via a staging database where I perform any data manipulations I require. I could have this case statement set the data in the MS SQL warehouse in the SSIS package. Which is what it looks like I will likely end up doing.

This package runs every minute and is very efficient. I don't like making changes to this package and was just hoping to be able to do this via DAX, for now at least. I'm DAX curious and am still learning, so like the challenge.

Many thanks,

Alister

[Anonymous · Accepted solution]

@AliH

Gotcha, that makes sense. I bet you could do this with a single SWITCH though. Something like:

CaseStatement =
SWITCH(
   TRUE(),
   AND(Status="T", OR(ShippingDate <= TODAY(), complete = 0)), "Trapping",
   AND(Status="T", OR(ShippingDate > TODAY(), complete <> 0)), "FutureTrapping"
)

With AND's and OR's, you can specify the same logic as your SQL. If all of these columns are in different tables, make sure to use RELATED if they have a valid relationship.

Hope this helps,

Parker