Forum Discussion
Need Help with DAX IF Condition
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.
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
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 ) / 1000Note that CONTAINSSTRING is not case-sensitive, so you don't need upper.
6 Replies
- AlexisOlson
Super User
You'll need to share the DAX that is throwing the error.
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.