DAX Help - Filter Table with Multiple selection options

[Anonymous]

Community,

I am seeking help with filtering a table by looking at the values across a few columns. Below is the table I am working with.

I am trying to filter the table with a Measure such that if [Pass.Yaw]=100 then find the row where [Atd.Yaw] and [Data.Yaw] match, or if [Pass.Splitter]=100 then find the row where[Atd.Splitter] and [Data.Splitter] match.

Click Here for the sample PBIX file.

Thanks for any possible solutions!

[amitchandak · Accepted solution]

@Anonymous 

You can use like this in filter

filter(Table, (Table[Pass.Yaw]=100 && Table[Atd.Yaw] = Table[Data.Yaw] ) || (Table[Pass.Splitter]=100 && Table[Atd.Splitter] =Table[Data.Splitter] ))

Like

Measure = calculate(sum(table[value]),filter(Table, (Table[Pass.Yaw]=100 && Table[Atd.Yaw] = Table[Data.Yaw] ) || (Table[Pass.Splitter]=100 && Table[Atd.Splitter] =Table[Data.Splitter] )))

[amitchandak · Accepted solution]

@Anonymous 

You can use like this in filter

filter(Table, (Table[Pass.Yaw]=100 && Table[Atd.Yaw] = Table[Data.Yaw] ) || (Table[Pass.Splitter]=100 && Table[Atd.Splitter] =Table[Data.Splitter] ))

Like

Measure = calculate(sum(table[value]),filter(Table, (Table[Pass.Yaw]=100 && Table[Atd.Yaw] = Table[Data.Yaw] ) || (Table[Pass.Splitter]=100 && Table[Atd.Splitter] =Table[Data.Splitter] )))

[Anonymous]

@amitchandak 

Thanks for the response!

This solution identifies the correct row of data that I was seeking when all the table attributes are shown. But when reviewing a reduced table (single line table) the output data is not as I would expect.

TheDAX forCL3 is

CL3 = 
AVERAGE(TestData[SlopeCLxx^2])*AVERAGE(TestData[Data.V^2])+
AVERAGE(TestData[SlopeCLxx])*AVERAGE(TestData[Data.V])+
AVERAGE(TestData[Slope.CLIncpt])

It looks like I am getting the average of all the data for [Atd.AtdID] and not the single row that I am wanting. Any thoughts on how to resolve this? Do I need to apply the filter information in theCL3 measure? 

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

Try to edit your "CL3" measure like the following DAX:

CL3 =
AVERAGEX (
    ALL ( TestData ),
    TestData[SlopeCLxx^2] * TestData[Data.V^2] + TestData[SlopeCLxx] * TestData[Data.V] + TestData[Slope.CLIncpt]
)

Or you could create a new measure:

Measure =
AVERAGEX (
    ALL ( TestData ),
    [CL3]
)

[Anonymous]

@v-eachen-msft , @amitchandak 

Thanks for the DAX help!