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

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.