Max value in column based on distinct count another column

[magnusks]

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 this, some of the projects are listed several times, thus I cannot remove duplicates in the power query since I need them for others measures (i.e. number of applications sent for external funding). 

Lets get back to the problem at hand. I want to calculate which "PI" has the most number of "projects".

Table name: Allprojects

Project	PI
A	Anna
B	Anna
A	Anna
C	John
D	Mike
E	Elsa

I have tried this measure (see below), but it returns that Anna has 3 projects. But as you can see, she only has 2 distinct ones. I want the output to be: Anna: 2

Mostprojects PI =

var _table=

SUMMARIZE(

    'Allprojects','Allprojects'[PI],

    "Count",COUNTX(FILTER(ALL('Allprojects'),'Allprojects'[PI]=MAX('Allprojects'[PI])),'Allprojects'[PI]))

var _table2=

FILTER(

    _table,[Count]=MAXX(_table,[Count]))

return

    MAXX(_table2,'Allprojects'[PI])&": "&MAXX(_table2,[Count] )

Thanks

[v-weiyan1-msft]

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.

[magnusks]

hi @talespin 

Thanks. I tried the dax you suggested, but it only returns the no of distinct projects. I want it to also dispay the name of the PI with the most distinct prosjects. 

Thanks

[talespin]

hi @magnusks ,

Please share what result you expect, This measure works for both visuals in screenshot. The count is at PI level.

[magnusks]

Hi @talespin 

Im expecting a result that allows me to create a card that looks something like this

(But with 2 instead of 3).

Thanks

[talespin · Accepted solution]

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

[magnusks]

Thank you - that worked 👍

[talespin]

@magnusks 

You're welcome.

[talespin]

hi @magnusks 

Please use this.

PI Distinct Count =

VAR _PI = SELECTEDVALUE( TestTbl4[PI])

RETURN CALCULATE( DISTINCTCOUNT(TestTbl4[Project]), REMOVEFILTERS(TestTbl4[Project]) )