IF statement as measure

[o59393]

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.

[PaulDBrown · Accepted solution]

@o59393 

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

[KHorseman]

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.

[o59393]

hi!

Still got an error, I attach the pbix

https://1drv.ms/u/s!ApgeWwGTKtFdhjBPLkc9NFaUMyzB?e=YVZrx5

Thanks!

[PaulDBrown]

@o59393 

Try subsituting the HASONEVALUE for SELECTEDVALUE in your if statement

[o59393]

Hi @PaulDBrown 

Still got an error 😞

I attached the pbi in the previous message just in case.

Thanks.

[PaulDBrown · Accepted solution]

@o59393 

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

[o59393]

@PaulDBrown 

Still cant display:

Thanks!

[v-lili6-msft · Accepted solution]

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