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.

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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

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