topic Re: Maximum value of a column filtered by other columns in DAX Commands and Tips

Maximum value of a column filtered by other columns

XaviOV — Thu, 11 Feb 2021 14:16:40 GMT

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.

Re: Maximum value of a column filtered by other columns

amitchandak — Thu, 11 Feb 2021 14:57:51 GMT

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

Re: Maximum value of a column filtered by other columns

XaviOV — Fri, 12 Feb 2021 10:34:58 GMT

Hi amitchandak ,

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

Regards,

Xavi

Re: Maximum value of a column filtered by other columns

amitchandak — Fri, 12 Feb 2021 10:43:27 GMT

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

Re: Maximum value of a column filtered by other columns

XaviOV — Fri, 12 Feb 2021 10:52:08 GMT

Hi amitchandak

In the second IF beacause there are two 0?

Thanks!!

Xavi

Re: Maximum value of a column filtered by other columns

XaviOV — Fri, 12 Feb 2021 11:06:31 GMT

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

Re: Maximum value of a column filtered by other columns

amitchandak — Fri, 12 Feb 2021 11:21:29 GMT

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

Re: Maximum value of a column filtered by other columns

XaviOV — Fri, 12 Feb 2021 11:44:10 GMT

Hi amitchandak

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

Regards,

Xavi

Re: Maximum value of a column filtered by other columns

XaviOV — Fri, 12 Feb 2021 12:34:52 GMT

Hi amitchandak

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

Regards,

Xavi

Re: Maximum value of a column filtered by other columns

XaviOV — Fri, 12 Feb 2021 15:10:03 GMT

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

Regards!!!

Xavi

Re: Maximum value of a column filtered by other columns

amitchandak — Fri, 12 Feb 2021 16:05:10 GMT

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