Maximum value of a column filtered by other columns

[XaviOV]

Hi,

I have a table with these values and I need to filter the values with the example table (future), the result is column C

Actual:

Future:

Thanks!!

Xavi.

[amitchandak]

@XaviOV , a new column like

new column =
var _1 = countx(filter(Table, [ID_1] =earlier[ID_1] && [B] =0),[ID_1])+0
var _2 = MinX(filter(Table, [ID_1] =earlier[ID_1] ),[ID_0])
return
if([ID0] =_2 ,if(_1>0 0, 1),blank())

[XaviOV]

Hi @amitchandak  ,

When I write the formula appear an error, I think that function earlier need something, it's possible?

Regards,

Xavi

[amitchandak]

@XaviOV , My mistake missing paranthesis 

new column =
var _1 = countx(filter(Table, Table[ID_1] =earlier(Table[ID_1]) && [B] =0),[ID_1])+0
var _2 = MinX(filter(Table, [ID_1] =earlier(Table[ID_1]) ),[ID_0])
return
if([ID0] =_2 ,if(_1>0 0, 1),blank())

[XaviOV]

Hi @amitchandak 

I applied the formula that you write and this is the result, in this case in the file with the number of ID0 471635 and the row of new column should appear number 0

Regards,

Xavi

[XaviOV]

Hi @amitchandak 

In the second IF beacause there are two 0?

Thanks!!

Xavi

[amitchandak]

@XaviOV ,Can you share sample data and sample output in table format? 

[XaviOV]

Hi @amitchandak 

ID0 is Whole number
ID1 is Text
A is Text
B is Whole number

Regards,

Xavi

[XaviOV]

Hi @amitchandak 

I try to paste the table from Excel but appear a error.

Regards,

Xavi

[XaviOV · Accepted solution]

Hi @amitchandak 
The solution is there Remove Duplicates and Keep the Last Record with Power Query - Excelerator BI

Regards!!!

Xavi

[amitchandak]

@XaviOV , Hope you got the solution on that blog. Thanks for sharing.