Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX Help - Filter Table with Multiple selection options

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!

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    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] )))

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

    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]
)

     

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        v-eachen-msft
        Community Support
        6 years ago

        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]
)