Forum Discussion

dsalony's avatar
dsalony
Helper I
4 years ago
Solved

Indexing a value

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?

Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    4 years ago

    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.

3 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    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.

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    4 years ago

    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.