Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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) (...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    5 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
AlexisOlson
Icon for Super User rankSuper User
5 years ago

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.