Forum Discussion
Helper VDesign Dax formula - with Multiple Ifs condition
Hello All Power Bi Board Members,
I need one help in design Dax formula. Below my formula is in Excel, I want to convert this in Dax. Could anyone help/guide me on this..
Col AO name = Request Type
Col AP name = Time Taken
=IF(AND(AO2="MR",INT(24*AP2)>8),"> 8 Hrs",
IF(AND(AO2="MR",INT(24*AP2)<=8),INT(24*AP2),
IF(AND(AO2="MR Routine",AP2<TIMEVALUE("00:30:00")),"< 30 Min",
IF(AND(AO2="MR Routine",AP2>=TIMEVALUE("00:30:00"),AP2<TIMEVALUE("01:00:00")),"30 Min - 1 Hr",
IF(AND(AO2="MR Routine",AP2>=TIMEVALUE("01:00:00"),AP2<TIMEVALUE("01:30:00")),"1 Hr - 1.5 Hr",
IF(AND(AO2="MR Routine",AP2>=TIMEVALUE("01:30:00"),AP2<TIMEVALUE("02:00:00")),"1.5 Hr - 2 Hr",
IF(AND(AO2="MR Routine",AP2>=TIMEVALUE("02:00:00"),AP2<TIMEVALUE("02:30:00")),"2 Hr - 2.5 Hr",
IF(AND(AO2="MR Routine",AP2>=TIMEVALUE("02:30:00"),AP2<TIMEVALUE("03:00:00")),"2.5 Hr - 3 Hr",
IF(AND(AO2="MR Routine",AP2>=TIMEVALUE("03:00:00"),AP2<TIMEVALUE("03:30:00")),"3 Hr - 3.5 Hr",
IF(AND(AO2="MR Routine",AP2>=TIMEVALUE("03:30:00"),AP2<TIMEVALUE("04:00:00")),"3.5 Hr - 4 Hr",
IF(AND(AO2="MR Routine",AP2>=TIMEVALUE("04:00:00")),"> 4 Hr",
IF(AND(AO2="RN",INT(24*AP2)<8),"< 8 Hrs",
IF(AND(AO2="RN",INT(24*AP2)>=8,INT(24*AP2)<16),"8 - 16 hrs",
IF(AND(AO2="RN",INT(24*AP2)>=16,INT(24*AP2)<24),"16 - 24 hrs",
IF(AND(AO2="RN",INT(24*AP2)>=24,INT(24*AP2)<36),"24 hrs - 36 hrs",
IF(AND(AO2="RN",INT(24*AP2)>=36,INT(24*AP2)<48),"36 hrs - 48 hrs",
IF(AND(AO2="RN",INT(24*AP2)>=48),"> 48 hrs",
IF(AO2="NA","NA",INT(24*AP2)))))))))))))))))))
9 Replies
- amitchandak
Super User
PowerBIBeginer , AP2, and AO2 will be replaced with column name like [AP] and [AO]
Timevalue function with time
In place of so many if use switch true()
Switch(True(),
<Condition>, <Result>,
)
refer
Switch-Case statement of #PowerBI: https://www.youtube.com/watch?v=gelJWktlR80&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=56
v-yalanwu-msftCommunity Support
Hi, PowerBIBeginer ;
If you create a column by dax ,you could change the formula like belows:
if = SWITCH ( TRUE (), AND ( [Request Type] = "MR", INT ( 24 * [Time Taken] ) > 8 ), "> 8 Hrs", AND ( [Request Type] = "MR", INT ( 24 * [Time Taken] ) <= 8 ), INT ( 24 * [Time Taken] ), AND ( [Request Type] = "MR Routine", [Time Taken] < TIMEVALUE ( "00:30:00" ) ), "< 30 Min", [Request Type] = "MR Routine"&& [Time Taken] >= TIMEVALUE ( "00:30:00" )&& [Time Taken] < TIMEVALUE ( "01:00:00" ), "30 Min - 1 Hr", [Request Type] = "MR Routine"&& [Time Taken] >= TIMEVALUE ( "01:00:00" )&& [Time Taken] < TIMEVALUE ( "01:30:00" ), "1 Hr - 1.5 Hr", [Request Type] = "MR Routine"&& [Time Taken] >= TIMEVALUE ( "01:30:00" )&& [Time Taken] < TIMEVALUE ( "02:00:00" ), "1.5 Hr - 2 Hr", [Request Type] = "MR Routine"&& [Time Taken] >= TIMEVALUE ( "02:00:00" )&& [Time Taken] < TIMEVALUE ( "02:30:00" ), "2 Hr - 2.5 Hr", [Request Type] = "MR Routine"&& [Time Taken] >= TIMEVALUE ( "02:30:00" )&& [Time Taken] < TIMEVALUE ( "03:00:00" ), "2.5 Hr - 3 Hr", [Request Type] = "MR Routine"&& [Time Taken] >= TIMEVALUE ( "03:00:00" )&& [Time Taken] < TIMEVALUE ( "03:30:00" ), "3 Hr - 3.5 Hr", [Request Type] = "MR Routine"&& [Time Taken] >= TIMEVALUE ( "03:30:00" )&& [Time Taken] < TIMEVALUE ( "04:00:00" ), "3.5 Hr - 4 Hr", AND ( [Request Type] = "MR Routine", [Time Taken] >= TIMEVALUE ( "04:00:00" ) ), "> 4 Hr", AND ( [Request Type] = "RN", INT ( 24 * [Time Taken] ) < 8 ), "< 8 Hrs", [Request Type] = "RN"&& INT ( 24 * [Time Taken] ) >= 8&& INT ( 24 * [Time Taken] ) < 16, "8 - 16 hrs", [Request Type] = "RN"&& INT ( 24 * [Time Taken] ) >= 16&& INT ( 24 * [Time Taken] ) < 24, "16 - 24 hrs", [Request Type] = "RN"&& INT ( 24 * [Time Taken] ) >= 24&& INT ( 24 * [Time Taken] ) < 36, "24 hrs - 36 hrs", [Request Type] = "RN"&& INT ( 24 * [Time Taken] ) >= 36&& INT ( 24 * [Time Taken] ) < 48, "36 hrs - 48 hrs", [Request Type] = "RN"&& INT ( 24 * [Time Taken] ) >= 48, "> 48 hrs", [Request Type] = "NA", "NA",INT ( 24 * [Time Taken] ))if you create a measure by dax , you should modify it.
if = SWITCH ( TRUE (), AND ( MAX ( [Request Type] ) = "MR", INT ( 24 * MAX ( [Time Taken] ) ) > 8 ), "> 8 Hrs", AND ( MAX ( [Request Type] ) = "MR", INT ( 24 * MAX ( [Time Taken] ) ) <= 8 ), INT ( 24 * MAX ( [Time Taken] ) ), AND (MAX ( [Request Type] ) = "MR Routine",MAX ( [Time Taken] ) < TIMEVALUE ( "00:30:00" )), "< 30 Min", MAX ( [Request Type] ) = "MR Routine"&& MAX ( [Time Taken] ) >= TIMEVALUE ( "00:30:00" )&& MAX ( [Time Taken] ) < TIMEVALUE ( "01:00:00" ), "30 Min - 1 Hr", MAX ( [Request Type] ) = "MR Routine"&& MAX ( [Time Taken] ) >= TIMEVALUE ( "01:00:00" )&& MAX ( [Time Taken] ) < TIMEVALUE ( "01:30:00" ), "1 Hr - 1.5 Hr", MAX ( [Request Type] ) = "MR Routine"&& MAX ( [Time Taken] ) >= TIMEVALUE ( "01:30:00" )&& MAX ( [Time Taken] ) < TIMEVALUE ( "02:00:00" ), "1.5 Hr - 2 Hr", MAX ( [Request Type] ) = "MR Routine"&& MAX ( [Time Taken] ) >= TIMEVALUE ( "02:00:00" )&& MAX ( [Time Taken] ) < TIMEVALUE ( "02:30:00" ), "2 Hr - 2.5 Hr", MAX ( [Request Type] ) = "MR Routine"&& MAX ( [Time Taken] ) >= TIMEVALUE ( "02:30:00" )&& MAX ( [Time Taken] ) < TIMEVALUE ( "03:00:00" ), "2.5 Hr - 3 Hr", MAX ( [Request Type] ) = "MR Routine"&& MAX ( [Time Taken] ) >= TIMEVALUE ( "03:00:00" )&& MAX ( [Time Taken] ) < TIMEVALUE ( "03:30:00" ), "3 Hr - 3.5 Hr", MAX ( [Request Type] ) = "MR Routine"&& MAX ( [Time Taken] ) >= TIMEVALUE ( "03:30:00" )&& MAX ( [Time Taken] ) < TIMEVALUE ( "04:00:00" ), "3.5 Hr - 4 Hr", AND (MAX ( [Request Type] ) = "MR Routine",MAX ( [Time Taken] ) >= TIMEVALUE ( "04:00:00" )), "> 4 Hr", AND ( MAX ( [Request Type] ) = "RN", INT ( 24 * MAX ( [Time Taken] ) ) < 8 ), "< 8 Hrs", MAX ( [Request Type] ) = "RN"&& INT ( 24 * MAX ( [Time Taken] ) ) >= 8&& INT ( 24 * MAX ( [Time Taken] ) ) < 16, "8 - 16 hrs", MAX ( [Request Type] ) = "RN"&& INT ( 24 * MAX ( [Time Taken] ) ) >= 16&& INT ( 24 * MAX ( [Time Taken] ) ) < 24, "16 - 24 hrs", MAX ( [Request Type] ) = "RN"&& INT ( 24 * MAX ( [Time Taken] ) ) >= 24&& INT ( 24 * MAX ( [Time Taken] ) ) < 36, "24 hrs - 36 hrs", MAX ( [Request Type] ) = "RN"&& INT ( 24 * MAX ( [Time Taken] ) ) >= 36&& INT ( 24 * MAX ( [Time Taken] ) ) < 48, "36 hrs - 48 hrs", MAX ( [Request Type] ) = "RN"&& INT ( 24 * MAX ( [Time Taken] ) ) >= 48, "> 48 hrs", MAX ( [Request Type] ) = "NA", "NA",INT ( 24 * MAX ( [Time Taken] ) ))If not right ,can you share simple file or more details?
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
PowerBIBeginerThank You so much for providing so detailed answer and explanation.
I've applied this, but I'm getting this error..
"Expressions that yield variant data-type cannot be used to define calculated columns."
Could you please let me know wht to do..
- v-yalanwu-msft
Hi, PowerBIBeginer ;
Your dax cannot output both numbers and text. You have to pick one or the other.
To fix this, you can convert the number to text using the
FORMATfunction:INT ( 24 * MAX ( [Time Taken] ) )change it like belows:
FORMAT ( INT ( 24 * [Time Taken] ), "0" )Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- PowerBIBeginer
Thanks for again reply to my query. This is really appriciate.
But now after applying this, I'm getting error msg -
"DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values."
- v-yalanwu-msft
Hi, PowerBIBeginer ;
Can you share me simple file or screenshot about your data and error without sensitive information? It's not clear here what your [Time Taken] format is?
I suspect that there is a problem in INT (24 * [Time Taken]) >= 36 or [Time Taken] >= TIMEVALUE ("02:30:00") .
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- v-yalanwu-msft
Hi, PowerBIBeginer ;
1.The column in which we're trying to get this output is Text, such as "> 4 Hr" and "> 48 hrs";Because your output result has characters and letters, it must be text. In order to maintain consistency, a column can only have one type, which requires that the output results are all text. Therefore, the initial int (24 * ap2) does not meet the requirements, resulting in an error.
2.If your [Time Taken] data type = Decimal number and Format = whole number .
and Request type column data type Text.
I tested it correct，However, this error is displayed because of the data type, which means that the text cannot be compared with the number. Through your description, I did not find the comparison between the number and the text. So I still hope to share your code 、 errors and some simple data screenshots without sensitive information . thanks .
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. - PowerBIBeginer
amitchandak, Thanks for reply. Let me try something like this which is totally new for me..
