topic DAX query required for IF and IF Else condition of below case in DAX Commands and Tips

DAX query required for IF and IF Else condition of below case

Anonymous — Tue, 09 Jul 2024 11:27:10 GMT

Hi Team,

I am new to PBI, help me with this case!

IF type(col) = "AAA" & Date time(col) < 11AM then Pickup date = Date(col)-1
ELSE Pickup date = Date(col)

IF type(col) = "BBB" then Pickup date = Date(col)–1

IF type(col) = "CCC" then Pickup date = Date(col)

Value of "AAA" is having AAA_a, AAA_b, AAA_c, AAA_d, AAA_e ----AAA_n etc. So here we are calling the condition with "AAA". How do we calling "AAA" with one IF condition statement or any other.

Same like value of "BBB" & Value of "CCC".

Here date time(col) contains 24hrs, we required less than(< )upto 11AM then Pickup date = Date(col)-1, else Pickup date = Date(col).
Here Date(col) is calculated column.

So, kindly helpe me with the DAX query. below is the snapshot for your reference.

Re: DAX query required for IF and IF Else condition of below case

BeaBF — Tue, 09 Jul 2024 09:03:03 GMT

Anonymous Hi! Here an example code:

Pickup date =
VAR TypeCol = [type(col)]
VAR ColDateTime = [datetime(col)]
VAR TimeOfDay = TIME(HOUR(ColDateTime), MINUTE(ColDateTime), SECOND(ColDateTime))

RETURN
SWITCH(
TRUE(),
TypeCol = "AAA" && TimeOfDay < TIME(11, 0, 0), DATEVALUE(ColDateTime) - 1,
TypeCol = "AAA" && TimeOfDay >= TIME(11, 0, 0), DATEVALUE(ColDateTime),
TypeCol = "BBB", DATEVALUE(ColDateTime) - 1,
TypeCol = "CCC", DATEVALUE(ColDateTime)
)

if you provide sample data and more info, i can help you better.

BBF

Re: DAX query required for IF and IF Else condition of below case

Anonymous — Tue, 09 Jul 2024 11:26:16 GMT

Hi,
Thanks for your response. Below is the sample data is thier with snapshot. kindly help me.
1. here I have multiple "AAA" , "BBB" values and help me with the solution.

2. Time values are in decimal values.
BeaBF

Re: DAX query required for IF and IF Else condition of below case

BeaBF — Tue, 09 Jul 2024 11:50:42 GMT

Anonymous copy and paste here data, thx

Re: DAX query required for IF and IF Else condition of below case

Anonymous — Tue, 09 Jul 2024 12:26:44 GMT

type date time

AAA_a	2/22/2024	10.8
AAA_b	2/26/2024	4.75
AAA_c	1/16/2024	22.7
BBB_a	2/22/2024	15.8
BBB_b	2/12/2024	2.5
BBB_c	1/12/2024	23
BBB_d	1/29/2024	1.08
CCC	2/22/2024	0.9

Hi,
Thanks for your response. Below is the sample data is thier with snapshot. kindly help me.
1. here I have multiple "AAA" , "BBB" values and help me with the solution.

2. Time values are in decimal values.

Re: DAX query required for IF and IF Else condition of below case

BeaBF — Tue, 09 Jul 2024 12:51:43 GMT

Anonymous here the correct calculated column:

Pickup date =

VAR TypeCol = [type]

VAR DateCol = [date]

VAR TimeCol = [time]

VAR TimeAsFraction = TimeCol - INT(TimeCol)

VAR TimeAsTime = TIME(INT(TimeCol), INT(TimeAsFraction * 60), 0)

VAR DateTimeCol = DateCol + TimeAsTime

RETURN

SWITCH(

TRUE(),

LEFT(TypeCol, 3) = "AAA" && TimeAsTime < TIME(11, 0, 0), DateCol - 1,

LEFT(TypeCol, 3) = "AAA" && TimeAsTime >= TIME(11, 0, 0), DateCol,

LEFT(TypeCol, 3) = "BBB", DateCol - 1,

TypeCol = "CCC", DateCol,

DateCol

)

BBF

Re: DAX query required for IF and IF Else condition of below case

Anonymous — Tue, 09 Jul 2024 14:00:58 GMT

Hi,
Thanks for your response. everything working is fine.
But, date = blank, then getting the below snapshot of 12/29/1899 year date. kindly pls let me know how to change with 12/29/1899 year date to blank rows.

BeaBF

Re: DAX query required for IF and IF Else condition of below case

BeaBF — Tue, 09 Jul 2024 14:21:42 GMT

Anonymous accept my answer as a solution, please. Then, i didn't understand the problem with the date, can you try to explain me?

BBF

Re: DAX query required for IF and IF Else condition of below case

Anonymous — Tue, 09 Jul 2024 14:28:28 GMT

Hi,

Currently my Date column having "12/29/1899", how to change it with "blank" records. kindly help me with this pls. like below snapshot.
Date Updated_Date

12/29/1899
12/29/1899
12/29/1899
12/29/1899
12/29/1899

Re: DAX query required for IF and IF Else condition of below case

BeaBF — Tue, 09 Jul 2024 14:32:42 GMT

Anonymous Here:
Updated_Date = IF([Date] = DATE(1899, 12, 29), BLANK(), [Date])

but if the measure is correct, accept the message as solution.

BBF

Re: DAX query required for IF and IF Else condition of below case

BeaBF — Tue, 09 Jul 2024 14:48:15 GMT

Anonymous accept the message as a solution.