topic Re: For all kinds in one column pick value in second column based on aggregation in third in DAX Commands and Tips

For all kinds in one column pick value in second column based on aggregation in third

sslezic — Tue, 23 Jan 2024 20:41:43 GMT

I apologize if this is an obvious solution and has been covered many times before. There is just so much noise when searching for a simple solution.

The following is a representative table for the data I have:

I'd like: for each Type get Name that has max Value.

So in this case my result would be a table with two rows that have Name values Y and X in them. I don't care how much of the original rows I keep (I can always extract Names out of there).

Re: For all kinds in one column pick value in second column based on aggregation in third

Anonymous — Wed, 24 Jan 2024 01:43:26 GMT

Hi sslezic ,

Please follow these steps:

1.Create a measure to get the name of the maximum value corresponding to each type.

max_name = VAR _max_value = MAXX(FILTER(ALL('Table'),'Table'[Type] = MAX('Table'[Type])),'Table'[Value]) RETURN IF(MAX('Table'[Value]) = _max_value, MAX('Table'[Name]), BLANK())

2.The result obtained is shown below.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: For all kinds in one column pick value in second column based on aggregation in third

sslezic — Thu, 25 Jan 2024 16:59:30 GMT

Anonymous thank you for your reply. It does work as advertised as long as it's displayed along with the other values. Your solution did eventually help me along to my final implementation.

For posterity:

I ended up adding a new calculated column in my original table (or at least imported from the source):

GreatestValue = MAXX( FILTER( 'Table', 'Table'[Type] = EARLIER('Table'[Type])), 'Table'[Value] )

Then I generated a new table in my data set:

Table2 = SELECTCOLUMNS( CALCULATETABLE( Table, Table[Value] = Table[GreatestValue] ), "Type", Table[Type], "Name", Table[Name], "Value", Table[Value] )

I'm sure this can be done more elegantly (and if someone is up for the challenge, please feel free), but it does allow me to manipulate the data as needed in my visualizations.

Re: For all kinds in one column pick value in second column based on aggregation in third

sslezic — Thu, 25 Jan 2024 19:14:12 GMT

It seems I was premature in my celebration. As much as my previous solution worked in Desktop Power BI, I could not upload it to the service as the data behind my calculation table is a DirectQuery source from another Power BI dataset.

Final solution that did work was a new column on my DirectQuery table that does the following:

LargestValue = VAR Result = MAXX( FILTER( 'Table', 'Table'[Type] = EARLIER('Table'[Type])), 'Table'[Value] ) RETURN IF( 'Table'[Value] = Result, 'Table'[Value], BLANK() )

Then in my visual I just filter out blanks and everything works as I need it to.

I also realize that this is close to the original solution proposed, but with a few differences. Once, it's not a measure, but a new column. Two, it has less MAX's throughout.

Thank you Anonymous for the inspiration though.