Forum Discussion

o59393's avatar
o59393
Icon for Post Prodigy rankPost Prodigy
6 years ago
Solved

IF statement as measure

Hi all

 

I am doing an if measure instead of a calculated column.


The dax is pretty simple, if the column type contains the word ingredients do one sum, if the same column type contains the word ingredients, do another calculation.

 

dax is

 

Q/FS Total DAX = 

var _package = (SUM('Suppliers Compliance'[Audit Score (2]) + SUM('Suppliers Compliance'[CAP Score (5_x]) + SUM('Suppliers Compliance'[QF Score (20_x]) + SUM('Suppliers Compliance'[Score Threats _x0028]) + SUM('Suppliers Compliance'[Score Analysis _x002]) ) /70

var _ingredients = (SUM('Suppliers Compliance'[Audit Score (2]) + SUM('Suppliers Compliance'[CAP Score (5_x]) + SUM('Suppliers Compliance'[QF Score (20_x]) + SUM('Suppliers Compliance'[Score Threats _x0028]) ) /70

return

IF(HASONEVALUE('Suppliers Compliance'[Type])= "Ingredients",  _ingredients  , _package)

 

As seen I use 2 variables to store the values, then have the if to return the value

 

When i drag the measure I get this error:

 

 

The right table shows the formula within the table.

 

Any idea how to fix this?

 

Thanks.

Need Help
Show and Tell
  • PaulDBrown's avatar
    PaulDBrown
    6 years ago

    o59393 

    Sorry, I meant try the substitution in your orginal measure, (excluding the && FIRSTNONBLANK part...)

  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi  o59393 

    The problem is that the data type of these columns 'Suppliers Compliance'[Audit Score (2] , 'Suppliers Compliance'[CAP Score (5_x] ,'Suppliers Compliance'[QF Score (20_x] , 'Suppliers Compliance'[Score Threats _x0028] , 'Suppliers Compliance'[Score Analysis _x002] are Text.

    Then just change the data type of these columns to Number and use this formula

    Q/FS Total DAX = 

var _package = (SUM('Suppliers Compliance'[Audit Score (2]) + SUM('Suppliers Compliance'[CAP Score (5_x]) + SUM('Suppliers Compliance'[QF Score (20_x]) + SUM('Suppliers Compliance'[Score Threats _x0028]) + SUM('Suppliers Compliance'[Score Analysis _x002]) ) /70

var _ingredients = (SUM('Suppliers Compliance'[Audit Score (2]) + SUM('Suppliers Compliance'[CAP Score (5_x]) + SUM('Suppliers Compliance'[QF Score (20_x]) + SUM('Suppliers Compliance'[Score Threats _x0028]) ) /70

return

IF( SELECTEDVALUE( 'Suppliers Compliance'[Type] )   = "Ingredients", _ingredients  , _package)

     

    Result:

    Regards,

    Lin

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HASONEVALUE returns true or false, but the condition you've written is HASONEVALUE('Suppliers Compliance'[Type])= "Ingredients" which not only will never evaluate to true, but will almost certainly cause a type conversion error since it's comparing text to a boolean value. I think perhaps what you were thinking of would be better written as something like:

     

    IF(HASONEVALUE('Suppliers Compliance'[Type]) && FIRSTNONBLANK('Suppliers Compliance'[Type], 1) = "Ingredients"

     

    By the way if you ever get a visual error like that, click the See Details link in the error message and share the details too. That can sometimes narrow down the problem.