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

 

ProjectPI
AAnna
BAnna
AAnna
CJohn
DMike
EElsa

 

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

 

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

     

8 Replies

  • talespin's avatar
    talespin
    Icon for Solution Sage rankSolution Sage
    2 years ago

    hi magnusks 

     

    Please use this.

     

    PI Distinct Count =
    VAR _PI = SELECTEDVALUE( TestTbl4[PI])
    RETURN CALCULATE( DISTINCTCOUNT(TestTbl4[Project]), REMOVEFILTERS(TestTbl4[Project]) )
  • magnusks's avatar
    magnusks
    Icon for Helper I rankHelper I
    2 years ago

    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's avatar
      talespin
      Icon for Solution Sage rankSolution Sage
      2 years ago

      hi magnusks ,

       

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

       

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

        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

  • 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.