DAX measure if this, and this then that

[Oisdf]

Hi All,

I'm trying to use DAX to do IF this AND this, then that.

I have been using lots of IF statements inside eachother but it gets very messy. 

INVALID_FILTER MEASURE = 
IF ( 
   [TEST_TYPE] = 1 , ( IF ( SUM([QTY_FAILS]) >= SUM([THRESHOLD]) , 
   [QTY_FAILS] , 0 ) 
   ) , 
( IF ( 
   [TEST_TYPE] = 2 , ( IF ( SUM([INVALID_PERCENTAGE]) >= 10 , 
   [INVALID_PERCENTAGE] , 0 ) 
   ) , 
0 ) ) )

Here is a sample from my dataset:

I want to take the value of one column, and depending on its value, apply checks to other columns. 

Example, if test_type = 1, then I want it to check if qty_fails is >= than threshold. if yes then qty_fails, else 0.

if test_type = 2 then a different check. Hope is to filter out 0 values.

Sample code does not work, I was getting this error :

The value for 'INVALID_PERCENTAGE' cannot be determined. Either the column doesn't exist, or there is no current row for this column.

So then I added the table name preceeding like this: 'table'[INVALID_PERCENTAGE]

but then I got this error:

A single value for column 'INVALID_PERCENTAGE' in table 'table' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Does what I need make sense? I have no idea how to resolve the second error.

Thank you in advance.

[ebeery]

Hi @Oisdf - the second error means that in the context of the visual in which your measure is being used, there are multiple values for INVALID_PERCENTAGE.  

You either need to further refine the filter context in which your measure is being calculated, or take a different approach altogether.

One quick troubleshooting step - try wrapping table'[INVALID_PERCENTAGE] in SELECTEDVALUE() in your measure like below - if nothing else, it should become a little more apparent where your error is coming from.

SELECTEDVALUE(table'[INVALID_PERCENTAGE])

[Oisdf]

Thanks for the response @ebeery  , same error though. 
I know my method is probably unorthodox and messy, you mentioned a different approach, does anything cleaner come to mind? 

[v-easonf-msft · Accepted solution]

Hi, @Oisdf 

Is this the error message you are getting?

If [INVALID_PERCENTAGE] is a column field rather than a measure, please use 'sum' to encapsulate this field.

INVALID_FILTERMEASURE =
IF (
    [TEST_TYPE] = 1,
    ( IF ( SUM ( [QTY_FAILS] ) >= SUM ( [THRESHOLD] ), [QTY_FAILS], 0 ) ),
    (
        IF (
            [TEST_TYPE] = 2,
            ( IF ( SUM ( [INVALID_PERCENTAGE] ) >= 10, SUM ( [INVALID_PERCENTAGE] ), 0 ) ),
            0
        )
    )
)

Best Regards,
Community Support Team _ Eason