Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
PowerBIBeginer
Helper V
Helper V

Design Dax formula - with Multiple Ifs condition

‎09-29-2021 04:11 AM

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)))))))))))))))))))

 

Labels:
Message 1 of 10
928 Views
0
9 REPLIES 9
v-yalanwu-msft
Community Support
Community Support

‎10-06-2021 07:07 PM

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.

How to get your questions answered quickly -- How to provide sample data
Message 10 of 10
588 Views
v-yalanwu-msft
Community Support
Community Support

‎10-04-2021 06:35 PM

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 FORMAT function:

INT ( 24 * MAX ( [Time Taken] ) )

change it like belows:

FORMAT ( INT ( 24 * [Time Taken] ), "0" )

vyalanwumsft_0-1633397728433.png

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.

How to get your questions answered quickly -- How to provide sample data
Message 6 of 10
695 Views
PowerBIBeginer
Helper V
Helper V
In response to v-yalanwu-msft

‎10-05-2021 04:00 AM

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."

 

Message 7 of 10
678 Views
0
v-yalanwu-msft
Community Support
Community Support
In response to PowerBIBeginer

‎10-05-2021 06:18 PM

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.

How to get your questions answered quickly -- How to provide sample data
Message 8 of 10
651 Views
0
PowerBIBeginer
Helper V
Helper V
In response to v-yalanwu-msft

‎10-06-2021 05:31 AM

Hello Sir, Thanks for replying me.

 

I've some couple of question's.. could you pls suggest on the same..

1. The column in which we're trying to get this output, should be what data type?

= Numeric Or Text?

 

2. Time Taken column has below formatting.

data type = Decimal number

Format = whole number

Is this correct?

 

3. Request type column has below formatting.

data type = Text

Format = Text

Is this correct?

 

Let me please correct on this first.. Thanks

Message 9 of 10
614 Views
0
v-yalanwu-msft
Community Support
Community Support

‎10-03-2021 08:00 PM

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.

How to get your questions answered quickly -- How to provide sample data
Message 4 of 10
729 Views
PowerBIBeginer
Helper V
Helper V
In response to v-yalanwu-msft

‎10-04-2021 03:03 AM

Thank 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..

Message 5 of 10
710 Views
0
PowerBIBeginer
Helper V
Helper V

‎09-29-2021 05:16 AM

@amitchandak, Thanks for reply. Let me try something like this which is totally new for me..

Message 3 of 10
800 Views
0
amitchandak
Super User
Super User

‎09-29-2021 04:38 AM

@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



Message 2 of 10
859 Views

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All