Indexing a value

[dsalony]

I have data that has a value that has two attributes:

• the value is '# of people'
• one attribute is Building Name
• other attribute is Leader Name

so something like this:

the column 'primary' is the column I am trying to derive with DAX.  Using Excel, since it is cell-based, it is easy to accomplish using nested INDEX, MATCH, and MAX functions across the rows, but since PowerBI is column-based, I can't replicate.  I have the data structured in the first three columns.  how do I generate the fourth column, which returns the name of the leader cross-referenced with the building, who has the majority share of people in each building?

[v-jianboli-msft · Accepted solution]

Hi @dsalony ,

Please try:

Primary =

var _maxpeople = MAXX(FILTER('Table',[Building]=EARLIER('Table'[Building])),[People])

return MAXX(FILTER('Table',[Building]=EARLIER('Table'[Building])&&[People]=_maxpeople),[Leader])

Output:

Best Regards,

Jianbo Li

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

[dsalony]

thanks, that worked!

[Ashish_Mathur]

Hi,

This calculated column formula works

=LOOKUPVALUE(Data[Leader],Data[People],CALCULATE(MAX(Data[People]),FILTER(Data,Data[Building]=EARLIER(Data[Building]))),Data[Building],Data[Building])

Hope this helps.