Need Help with DAX IF Condition

[Anonymous]

Hi Community,

I'm trying to convert a Custom Column into a Measure in Power BI, but I can this error, can someone help me with this.

Error Message below.

Error Message: MdxScript(Model) (559, 42) Calculation error in measure '00_MEAS_RATEMIX'[06_Mix_Pack Type 2 Mix]: A table of multiple values was supplied where a single value was expected.

[9:17 PM] Kadagala, Kuber

IF ( MAX(Input[Channel]) = "All", 0, 1 )

* IF ( ( [NR/HL Country Base] ) = 0, 0, 1 )

* IF (

OR (

FIND ( "CENTRAL", UPPER (HASONEVALUE(Input[Channel])), 1, 0 ) > 0,

FIND ( "CENTRAL", UPPER (HASONEVALUE(Input[Customer])), 1, 0 ) > 0

),

0,

1

)

* IF (

OR (

MAX(Input[Brand]) = "RESIDUAL STOCK",

FIND ( "CENTRAL", UPPER (HASONEVALUE(Input[Brand])), 1, 0 ) > 0

),

0,

1

)

* IF ( ABS ( [NR/HL Channel Base] - [NR/HL Channel AC] ) > 500, 0, 1 ) / 1000

It is a Direct Query Mode so it has to be a measue, I cannot make it a column.

Thanks in advance

[v-jayw-msft]

Hi @Anonymous ,

It's hard to reproduce the scenario, can you share some sample data to us?

Best Regards,

Jay

[AlexisOlson]

You'll need to share the DAX that is throwing the error.

[Anonymous]

IF ( MAX(Input[Channel]) = "All", 0, 1 )

* IF ( ( [NR/HL Country Base] ) = 0, 0, 1 )

* IF (

OR (

FIND ( "CENTRAL", UPPER (HASONEVALUE(Input[Channel])), 1, 0 ) > 0,

FIND ( "CENTRAL", UPPER (HASONEVALUE(Input[Customer])), 1, 0 ) > 0

),

0,

1

)

* IF (

OR (

MAX(Input[Brand]) = "RESIDUAL STOCK",

FIND ( "CENTRAL", UPPER (HASONEVALUE(Input[Brand])), 1, 0 ) > 0

),

0,

1

)

* IF ( ABS ( [NR/HL Channel Base] - [NR/HL Channel AC] ) > 500, 0, 1 ) / 1000

[AlexisOlson]

In a calculated column, I'm guessing [NR/HL Channel Base] and [NR/HL Channel AC] are the values from that particular row. A measure does not have row context, so you need to specify some sort of aggregation to return a single value (e.g. SUM or MAX or SELECTEDVALUE).

As a side note, HASONEVALUE returns true or false, so UPPER ( True / False ) probably isn't what you actually want.

[Anonymous]

NR/HL Channel AC and Base are measures, so I did not include those in any aggregation. 

FIND ( "CENTRAL", UPPER (HASONEVALUE(Input[Channel])), 1, 0 ) > 0,

FIND ( "CENTRAL", UPPER (HASONEVALUE(Input[Customer])), 1, 0 ) > 0

),

0,

1

)

* IF (

OR (

MAX(Input[Brand]) = "RESIDUAL STOCK",

FIND ( "CENTRAL", UPPER (HASONEVALUE(Input[Brand])), 1, 0 ) > 0

The issue is around the MAX and HASNOVALUE Function, while it was a column it was an if condition, after changing to measue IF condition was not working, so I tried DISTINCT and HASNOVALUE but none of them seems to work.

[AlexisOlson · Accepted solution]

Ah. Yeah, measures shouldn't need extra aggregations.

I think your measure could be simplified quite a bit. I'd suggest starting from something like this:

MeasureName =
VAR Channel = SELECTEDVALUE ( Input[Channel] )
VAR Customer = SELECTEDVALUE ( Input[Customer] )
VAR Brand = SELECTEDVALUE ( Input[Brand] )
RETURN
    IF (
        Channel = "All"
            || [NR/HL Country Base] = 0
            || CONTAINSSTRING ( Channel, "CENTRAL" )
            || CONTAINSSTRING ( Customer, "CENTRAL" )
            || Brand = "RESIDUAL STOCK"
            || CONTAINSSTRING ( Brand, "CENTRAL" )
            || ABS ( [NR/HL Channel Base] - [NR/HL Channel AC] ) > 500,
        0,
        1
    ) / 1000

Note that CONTAINSSTRING is not case-sensitive, so you don't need upper.