The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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.
Solved! Go to Solution.
@Ayyappa5678 Here:
Updated_Date = IF([Date] = DATE(1899, 12, 29), BLANK(), [Date])
but if the measure is correct, accept the message as solution.
BBF
@Ayyappa5678 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
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
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.
@Ayyappa5678 here the correct calculated column:
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.
@Ayyappa5678 accept my answer as a solution, please. Then, i didn't understand the problem with the date, can you try to explain me?
BBF
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 |
@Ayyappa5678 Here:
Updated_Date = IF([Date] = DATE(1899, 12, 29), BLANK(), [Date])
but if the measure is correct, accept the message as solution.
BBF
User | Count |
---|---|
28 | |
10 | |
8 | |
6 | |
5 |
User | Count |
---|---|
33 | |
13 | |
12 | |
9 | |
7 |