cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AliH Frequent Visitor
Frequent Visitor

Obtuse SQL Case statement to DAX

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Anonymous
Not applicable

Re: Obtuse SQL Case statement to DAX

@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

3 REPLIES 3
Anonymous
Not applicable

Re: Obtuse SQL Case statement to DAX

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 Frequent Visitor
Frequent Visitor

Re: Obtuse SQL Case statement to DAX

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

Highlighted
Anonymous
Not applicable

Re: Obtuse SQL Case statement to DAX

@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