Sum of maximal value for specific name

[Maestro]

Moving from (pivot)tables in Excel to Power BI provides some struggles. I will try to show my struggle as detailed as needed. The case: as a KPI (Key Performance Indicator) in sports I need to know from longitudinal data IF a player passed the benchmark or not (providing a % by dividing passed/all cases). He only needs to pass the benchmark once during the year (multiple matches obviously; benchmark can be different depending on playing position).

Name	Match	Total distance covered (m)	Benchmark (m)	Passed (o=no;1=yes)
Player A	1	6000	5000	1
Player B	1	4000	5000	0
Player C	1	7000	6000	1
Player D	0 **	1500	-	-
Player A	2	5500	5000	1
Player B	2	4000	5000	0
Player C	2	5000	6000	0

** not enough playtime, so data is there, but not included for the KPI.

KPI = passed/total

In Excel I would insert a Pivot table on the table above and then use "Max of passed" providing for each player if he ever passed the benchmark and total amount of filled cells (so unique players in this pivot table). The pivot table in this example would look like:

Name	Max of passed
Player A	1
Player B	0
Player C	1

KPI: 2 out of 3=66.7%

Player D is not included in pivot table, since there is no valid data (0 or 1) Sum of Max of passed is needed, since 'just' sum of passed will provide 2 instead of 1 for player 1. 

In Power BI I provided a new measure  "amount_players" which is: DISTINCTCOUNT(query1[player]). This will show the amount of players that pass multiple filters (minimum of playing time, team and season). Now I need a measure "amount_passed" but using MAX(X), SUM or a combination does not deliver the right answer. The main struggle appears when a player is able to pass the benchmark for 3 matches; any use of sum wants to enter the player with a '3', but I only want to count him once. 

If I need to provide more context or sample data, please let me know! 

[VahidDM · Accepted solution]

Hi @Maestro 

Please try this measure: [Edited]

amount_passed =
COUNTROWS (
    SUMMARIZE (
        FILTER ( 'Table', 'Table'[Passed] = 1 ),
        'Table'[Name],
        "pass", MAX ( 'Table'[Passed] )
    )
)

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos  !!

[VahidDM · Accepted solution]

Hi @Maestro 

Please try this measure: [Edited]

amount_passed =
COUNTROWS (
    SUMMARIZE (
        FILTER ( 'Table', 'Table'[Passed] = 1 ),
        'Table'[Name],
        "pass", MAX ( 'Table'[Passed] )
    )
)

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos  !!

[Maestro · Accepted solution]

Thanks for your quick response @VahidDM. It does not work yet; the error (MdxScript(model)(10,80) that in the DAX formula they can't  compare Integer (pass yes, no or empty) with Text/String (player name). They suggest to use the VALUE or FORMAT function, but that would only work if I replace Name by a ID-number. Any suggestions to make it work while still using the playernames as strings? 

EDIT: it worked, after replacing the "1" with 1 as boolean expression within the FILTER: 

FILTER ( 'Table', 'Table'[Passed] = 1 ),

 Thanks!

[VahidDM]

Happy to see you sorted it out.  !!