Forum Discussion
Identifying a max value in a large data set
Hi
I have a data table with a list of tasks and clients - each task has an outcome and a client can have more than one of the same task allocated to them - I want to produce a table where it counts each client only once and allocates them to the highest ranking outcome.
As an example
| Client Name | Task | Task Outcome | Outcome Rank |
| Joe Bloggs (123) | Task A | Meeting | 5 |
| Joe Bloggs (123) | Task A | No Response | 1 |
| Jane Doe (456) | Task A | Follow Up | 3 |
| Jane Doe (456) | Task A | No Response | 1 |
| John Smith (789) | Task A | Meeting | 5 |
I would want the table I'm showing to reflect this, so Jane is counted as a follow up and Joe and John are counted as meetings, no one is counted as No Response
| Outcome | Clients |
| Meeting | 2 |
| Follow Up | 1 |
| No Response | 0 |
Thank you in advance
1 Reply
If you have the outcomes in a dimension table linked to the main fact table then you can use
Clients per outcome = VAR SummaryTable = ADDCOLUMNS( ALL( 'Fact Table'[Client Name]), "@best task", CALCULATE( MAX( 'Fact Table'[Outcome Rank])) ) RETURN COUNTROWS( FILTER( SummaryTable, [@best task] IN VALUES( 'Dim Task'[Outcome Rank])))Please see the attached PBIX
