Filter Multiple Tables

[Anonymous]

Hi All,

Assume a table as follows.

Table 1
Table 1

Table 2
Table 2

I need output considering following steps:

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

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

//Please ignore 12 .

3) From O/P of Step 2 I need to select by Max R2.
Thus result shall be  ID=2.

4) Select Corresponding Final Title from Table 1 for ID=2

Help is appriciated.

Regards

Ayush

[v-diye-msft]

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]

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]

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]

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 .

[v-diye-msft · Accepted solution]

Hi @Anonymous 

check this one:

pbix attached.

[Anonymous]

Hi @v-diye-msft 

I believe I have one more case here:

If I have multiple values in Table 1 where combination of R1 and R2 is max ,then i need - 
Measure 4 values as H,I currently it will only show I as we have taken maxx.

I just need Output of Measure 4 as H,I now as we have 2 rows where the combination is maximum instead of 1 previously.

Thanks in advance.

[Anonymous]

Hey @v-diye-msft 

thanks for the help here. 

This is what i somewhat needed, will have to implement this on a larger scale now.

[amitchandak]

Try like

measure =
var _max =maxx(Table2,Table2[R1])
return
calculate(countrows(Table2),filter(Table2,Table2[R1] =_max))

measure =
var _max =maxx(Table2,Table2[R2])
return
calculate(countrows(Table2),filter(Table2,Table2[R2] =_max))

[Anonymous]

Hi @amitchandak 

The mentioned measure would return me the Count of Rows .

I need to get the exact ID from Table 2 which has highest combination i.e. R1=10 and R2=10.
The measure for CountRow returns the no of rows.

Regards

Ayush