topic Re: Switch with dates and text values/format in DAX Commands and Tips

Switch with dates and text values/format

jmetf150 — Thu, 01 Feb 2024 20:23:20 GMT

I am trying to write a SWITCH function that looks at a TEXT column ( [RS] ), and depending on the text value "OT", then compares between two other DATE columns ( [RDD] & [RCD] )to derive another TEXT column.

Cond = switch( 'R'[RS]="OT", 'R'[RDD]<='R'[RCD], "PD", "LLT" )

Re: Switch with dates and text values/format

Greg_Deckler — Thu, 01 Feb 2024 20:39:30 GMT

jmetf150 I think you want:

Cond = switch(TRUE(), 'R'[RS]="OT", IF('R'[RDD]<='R'[RCD], "PD", "LLT") ) or Cond = switch('R'[RS], "OT", IF('R'[RDD]<='R'[RCD], "PD", "LLT") )

Re: Switch with dates and text values/format

jmetf150 — Thu, 01 Feb 2024 22:57:53 GMT

Deleted so not to confuse anyone

Re: Switch with dates and text values/format

jmetf150 — Thu, 01 Feb 2024 22:55:57 GMT

Ok....I figured it out...my inital formula was incorrect...I was using an "="OT"" sign when I should have been using a "<>""OT".....Thank you very much for the soltuion Greg!

Re: Switch with dates and text values/format

jmetf150 — Fri, 02 Feb 2024 03:11:49 GMT

Greg_Deckler can you explain the difference between both of your solutions and when you would use one over the other?

Re: Switch with dates and text values/format

Greg_Deckler — Fri, 02 Feb 2024 15:20:38 GMT

jmetf150 Well, you would use a "normal" SWITCH statement when you have different values you want to return for different values in a column for example. There's no complex logic required in terms of when you want to return which values. So, let's say you have a column that can contain Pickle, Banana or Cucumber and you want to return 1 for Pickle, 2 for Banana and 3 for Cucumber. This is the second example.

The 1st example, the SWITCH(TRUE() ) statement is used when you have complex criteria, so return 1 if Pickle and the Value is > 30 for example.

Also note that in this specific use case, you could have used an IF statement,

IF( 'R'[RS]="OT" && 'R'[RDD]<='R'[RCD], "PD", "LLT")

Re: Switch with dates and text values/format

jmetf150 — Tue, 06 Feb 2024 23:17:28 GMT

Greg_Deckler to piggy back on this topic on IF/SWITCH, how would I do write the below formula if if I wanted to see orders that are "Late" (i.e. arriving after our need date) and if they are late, are they late in the past (i.e. PO del was last month) or are they late in the future (ie Need date is next month but current PO Due date is even after that).

The below is what I have know

LateStatus = if(ZMMAE[OT X 2 to Cur MRP Need]="Late" && if(ZMMAE[Del Date]>ZMMAE[Cur MRP Need], "Late in Past" && if(ZMMAE[Del Date]<ZMMAE[Cur MRP Need],"Late in the Future",""))

and I keep getting "Too few arguments were passed to the IF cunction.minimum argument is 2....".

Re: Switch with dates and text values/format

Greg_Deckler — Thu, 08 Feb 2024 13:13:33 GMT

jmetf150 Try:

LateStatus = SWITCH( TRUE(), ZMMAE[OT X 2 to Cur MRP Need]="Late" && ZMMAE[Del Date]>ZMMAE[Cur MRP Need], "Late in Past", ZMMAE[OT X 2 to Cur MRP Need]="Late" && ZMMAE[Del Date]<ZMMAE[Cur MRP Need],"Late in the Future", "" )

Re: Switch with dates and text values/format

jmetf150 — Fri, 09 Feb 2024 17:20:05 GMT

That is working to only pull the "Late" orders but it is saying all of the late orders are "In THe Past". I am fairly certain that is incorrect but the source data consists of over 7K lines so let me find one that is "Late in the Future" and see what it is pulling that way.