Forum Discussion
magnusks
Helper I
Helper IMax 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...
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])
