Forum Discussion

magnusks's avatar
magnusks
Icon for Helper I rankHelper I
2 years ago
Solved

Max value in column based on distinct count another column

Hi,   I have a table consisting of title of projects and the principal investigator (PI) for those projects. The projects in this table are projects that have applied for external funding. Due to t...
  • talespin's avatar
    talespin
    2 years ago

    hi magnusks 

     

    Reposting. This is a better solution.

     

    PI Name with max Count = No matter what columns you add remove. It will always give you PI with max distinct count along with distincg count. You can modify the RETURN statement to return one or the other if you want.
     
     
    PI Name with max Count =

    VAR _Summ =
    ADDCOLUMNS(
                ALL(TestTbl4[PI]),
                "@DCountProject",
                VAR _PI = [PI]
                RETURN CALCULATE(DISTINCTCOUNT(TestTbl4[Project]), REMOVEFILTERS(TestTbl4), TestTbl4[PI] = _PI)
    )
    VAR _TOP = TOPN(1, _Summ, [@DCountProject], DESC, [PI], ASC)

    RETURN SELECTCOLUMNS( _TOP, "@PI", [PI])&" - "&SELECTCOLUMNS( _TOP, "@DistinctCount", [@DCountProject])
     

     

v-weiyan1-msft's avatar
v-weiyan1-msft
Icon for Community Support rankCommunity Support
2 years ago

Hi magnusks ,

 

talespin nice method! And based on the sample and description you provided, you may also consider using the following code.

Mostprojects PI = 
var _table=
SUMMARIZE(
    'Table','Table'[PI],
    "Count",CALCULATE(DISTINCTCOUNT('Table'[Project]),ALLEXCEPT('Table','Table'[PI])))
var _table2=
FILTER(
    _table,[Count]=MAXX(_table,[Count]))
return
    MAXX(_table2,'Table'[PI])&": "&MAXX(_table2,[Count] )

Result is as below.

 

Best Regards,
Yulia Yan

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