Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Getting a single employee name

Hello everyone.

 

In the past i have worked in mysql, power bi dax is new for me. I am facing problem in solving following problem.

This is a help desk table in which i want to get the name of the concerned person who have completed most of the tickets. 

 

I have a table with column ID (unique), Status, Concerned Person. 

 ID= saves unique number assigned to it

Status= Completed, In Process, Rejected

Concerned Person= Name of the employee

 

I want to get concerned person name against highest number of distinct count (ID) where status is equal to completed.

 

 

 

 

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, Anonymous

    If there are two or more Concerned Person with the highest number of distinct count (ID), Does result display blank or all?

    If it is blank, you could use formula as ryan_mayu above,

    and if it is all, You could use this formula:

    Measure = 
VAR _table =
    SUMMARIZE (
        FILTER ( Sheet8, Sheet8[status] = "completed" ),
        Sheet8[Concerned Person],
        "ordernumber", DISTINCTCOUNT ( Sheet8[ID] )
    )
VAR maxorder =
    MAXX ( _table, [ordernumber] )
RETURN
    CALCULATE (
        CONCATENATEX ( VALUES ( Sheet8[Concerned Person] ), [Concerned Person], "," ),
        FILTER ( _table, [ordernumber] = maxorder )
    )

     

    Best Regards,
    Lin

     

     

     

     

6 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    7 years ago

    Anonymous

     

    You can try the coding below to see if this meets your requirements. Thanks.

     

    Measure = 
VAR _table=SUMMARIZE(FILTER(Sheet8,Sheet8[status]="complete"),Sheet8[person],"ordernumber",DISTINCTCOUNT(Sheet8[order]))
VAR maxorder=MAXX(_table,[ordernumber])
return CALCULATE(SELECTEDVALUE(Sheet8[person]),FILTER(_table,[ordernumber]=maxorder))

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      ryan_mayu Member

      I have tried this solution but it is returning BLANK result in the card visual

      • ryan_mayu's avatar
        ryan_mayu
        Icon for Super User rankSuper User
        7 years ago

        Anonymous 

         

        could you please share the screenshot of your sample data? Maybe you can also try Lin's solution. Thanks

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    hi, Anonymous

    If there are two or more Concerned Person with the highest number of distinct count (ID), Does result display blank or all?

    If it is blank, you could use formula as ryan_mayu above,

    and if it is all, You could use this formula:

    Measure = 
VAR _table =
    SUMMARIZE (
        FILTER ( Sheet8, Sheet8[status] = "completed" ),
        Sheet8[Concerned Person],
        "ordernumber", DISTINCTCOUNT ( Sheet8[ID] )
    )
VAR maxorder =
    MAXX ( _table, [ordernumber] )
RETURN
    CALCULATE (
        CONCATENATEX ( VALUES ( Sheet8[Concerned Person] ), [Concerned Person], "," ),
        FILTER ( _table, [ordernumber] = maxorder )
    )

     

    Best Regards,
    Lin