Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter Multiple Tables

Hi All, Assume a table as follows. Table 1 Table 2                       I need output considering following steps:   1) Select ID from Table 2 where R1=MAX(R1) 2) Out...
Need Help
v-diye-msft's avatar
v-diye-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous 

 

1) Select ID from Table 2 where R1=MAX(R1)

2) Output will be 1,2 and 8 ,  as MAX of R1=10

It's incorrect, coz MAX of R1=12 in your table.  I changed the 12 as 9 as your logic.

Then kindly find my results below:

Pbix attached.

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi,

That did help me but if there is a case as follows the Measure 3 throws Blank.

 

 

 

 

 

 

 

 

 

 

I have edited R2 and inserted 20, it should still give me output as ID=2 , but instead shows blank.
v-diye-msft 

  • v-diye-msft's avatar
    v-diye-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous 

     

    When you insert 20, the ID= 9 of MAX(R2), based on your previous description:

     

    3) From O/P of Step 2 I need to select by Max R2.

    From the ID results of MAX(R1), which is 1,2,8,  9 is definitely out of the range. that's why it's blank. 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi v-diye-msft ,

      I agree with your point.
      Please consider thew below scenario.
      For ID 1,2,8 I need the following output too. (Measure 3 needs to be modified)

      In Measure 3 value I need Output as (Id=2) because (my requirement) ,  For Max value in R1 , first I need all values in R2. (Like a nested filter)
      1) Table2 (MAX)R1=10, thus we have ID= (1,2,8)

      2)  For ID=(1,2,8) in table2 we need to find all R2.


      3) Thus, R2=(9,10,9) for R1=(1,2,8) respectively.       <---------// Sets


      4) Now, Measure 3 shall return me all ID of Table 2 where R2 is maximum from above selected set not the orignal table.
          Means Measure 3 shall be equal to Id=2 as we have (R1=10,R2=10) which is maximum combination out of the sets .

      5) Then Measure 4 shall give me Final title =I   (as Table1.ID=TAble2.ID=2)

      Really appreciate your time .