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

[sslezic]

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

[sslezic · Accepted solution]

@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.

[Anonymous]

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.

[sslezic · Accepted solution]

@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.

[sslezic · Accepted solution]

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.