Getting a single employee name

[Anonymous]

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.

[v-lili6-msft · Accepted solution]

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

[ryan_mayu]

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

ryan_mayu 

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

[ryan_mayu]

@Anonymous 

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

[Anonymous]

@ryan_mayu 

Dear, 

I have tried Lin's solution and it is working fine. Thanks for your input too. I am working on your solution to see where i am missing.

Thanks alot! 

[ryan_mayu]

@Anonymous 

You are welcome. Actually Lin and I used the similar solution for you. The difference is that she thought deeper than me. I didn't consider the situation that two cusomers have the same maximum order numbers.

Thanks