topic Re: Max value in column based on distinct count another column in DAX Commands and Tips

Max value in column based on distinct count another column

magnusks — Wed, 07 Feb 2024 11:53:40 GMT

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

Re: Max value in column based on distinct count another column

talespin — Wed, 07 Feb 2024 12:25:30 GMT

hi magnusks

Please use this.

PI Distinct Count =

VAR _PI = SELECTEDVALUE( TestTbl4[PI])

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

Re: Max value in column based on distinct count another column

magnusks — Wed, 07 Feb 2024 12:33:12 GMT

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

Re: Max value in column based on distinct count another column

talespin — Wed, 07 Feb 2024 12:39:31 GMT

hi magnusks ,

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

Re: Max value in column based on distinct count another column

magnusks — Thu, 08 Feb 2024 06:59:26 GMT

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

Re: Max value in column based on distinct count another column

talespin — Thu, 08 Feb 2024 08:23:37 GMT

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

Re: Max value in column based on distinct count another column

v-weiyan1-msft — Thu, 08 Feb 2024 09:00:17 GMT

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.

Re: Max value in column based on distinct count another column

magnusks — Thu, 08 Feb 2024 12:36:09 GMT

Thank you - that worked 👍

Re: Max value in column based on distinct count another column

talespin — Thu, 08 Feb 2024 12:43:09 GMT

magnusks

You're welcome.