DAX

[KT9]

Hi all, I am new to power BI. trying to create a calculated column using DAX. But its throwing me the error says that " Expressions that yield variant data-type cannot be used to define calculated columns". Please help. Thanks in advance!

The Rule is
If Final = Negative, then Negative
If Final = positive AND type = ABCD, then display the associated value in numeric_result(Here numeric_result is a column name in sql query and it contains decimal value)

Result =
SWITCH(TRUE(),
Tablename[Final] = "Negative", "Negative",
Tablename[Final] = "Positive" && Tablename[Type] = "ABCD", max(Tablename[Numeric_Result]))

[123abc]

It seems like you're encountering an error in your DAX expression because the MAX() function expects a column of values to return the maximum from, but you're passing a single value, which is not allowed in calculated columns.

To fix this, you can use an IF() statement instead of MAX(). Here's how you can adjust your DAX expression:

Result =
SWITCH(
TRUE(),
Tablename[Final] = "Negative", "Negative",
AND(Tablename[Final] = "Positive", Tablename[Type] = "ABCD"), Tablename[Numeric_Result],
BLANK() // If none of the conditions are met, return blank
)

This DAX expression follows your specified rules:

• If Final is "Negative", the result is "Negative".
• If Final is "Positive" and Type is "ABCD", the result is the value in the Numeric_Result column.
• If none of the conditions are met, it returns blank.

Make sure to replace "Tablename" with the actual name of your table in Power BI. This expression should work in creating the calculated column you desire.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[KT9]

Hi @123abc ,

Thank you so much for your response and the clear clarrification. I tried this DAX formula. But it throws an error says that "Function 'switch' does not support comparing values of type True/False with values of type Text. Consider using the VALUE or FORMAT funtion to convert one of the values."

  The Number_Result column contains the values like

0.000

1.099

-0.999

67.777

[Jihwan_Kim]

Hi,

Please try something like below if it suits your requirement.

Result CC =
SWITCH (
    TRUE (),
    Tablename[Final] = "Negative", "Negative",
    Tablename[Final] = "Positive"
        && Tablename[Type] = "ABCD", FORMAT ( MAX ( Tablename[Numeric_Result] ), "#,#0.00" )
)

[KT9]

Hi @Jihwan_Kim ,

Thank you so much for your response.  sorry i didn't communicate well. In Row 2 I am trying to write the code code without MAX function( to return largest no of value). But it is throwing the error ( Function "FORMAT" is not allowed as part of calculated column DAX expressions on DirectQuery models.) Please help.Thank you!

My exactl rule for the calculated column is ( using SQL server as a database)

1. If Final = Negative, then Negative

2. If Final = positive AND type = ABCD, then display the associated value in numeric_result (numeric_result is column name in sqlserver with decimal datatype values like 0.000 , -0.999)

3. IF Final = positive AND type = XYZ, then Positive

4. If Final = In Range, then In Range

[amitchandak]

@KT9 , seem like Numeric_Result a number data type. You can not have two datatype in the same column. I added additional code to convert it to a string

Result =
SWITCH(TRUE(),
Tablename[Final] = "Negative", "Negative",
Tablename[Final] = "Positive" && Tablename[Type] = "ABCD", max(Tablename[Numeric_Result]) & "" )