Forum Discussion

AliH's avatar
AliH
Frequent Visitor
8 years ago
Solved

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. ...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

AliH's avatar
AliH
Frequent Visitor
8 years ago

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's avatar
Anonymous
Not applicable
8 years ago

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