Need help creating a table visual based on matching criteria

[sedric1]

I am not sure if this concept is possible based on my data layout but would love feedback.

I have table in the following structure (sports data) that I'm trying to fiilter and group based on multiple matching criteria:

The matching criteria would be to find all rows with the same week, year, and team (all must match from row to row). The filter would then need to only show rows where the 'fd points' column is greater than 20. I thought maybe the functions summarize or groupby would make this work but I couldnt figure out how. Of the above table, the following two rows would return if successful:

If this is possible, I would like to return the above sample output as a single row of data, where a new column is created to hold the 2nd "player" information. For example 

If the single line is not possible, having multiple lines with the matching would still be super helpful. Thanks!

[Greg_Deckler]

@sedric1 - If you could post that data as text in a table that would be better, could give you a precise answer. But, try these measures:

Matching Player =
  VAR __Week = MAX([week])
  VAR __Year = MAX([year])
  VAR __Team = MAX([team])
  VAR __Player = MAX([player_name])
RETURN
  MAXX(FILTER('Table',[week]=__Week && [year]=__Year && [team]=__Team && [player_name]<>__Player),[player_name])

Matching Position =
  VAR __Week = MAX([week])
  VAR __Year = MAX([year])
  VAR __Team = MAX([team])
  VAR __Player = MAX([player_name])
RETURN
  MAXX(FILTER('Table',[week]=__Week && [year]=__Year && [team]=__Team && [player_name]=[Maching Player],[position])

Matching Points =
  VAR __Week = MAX([week])
  VAR __Year = MAX([year])
  VAR __Team = MAX([team])
  VAR __Player = MAX([player_name])
RETURN
  MAXX(FILTER('Table',[week]=__Week && [year]=__Year && [team]=__Team && [player_name]=[Maching Player],[fd points])

[sedric1]

Sorry for the delay- i thought I posted this table earlier this evening, but I am not seeing my last attempt, so reposting the data table now:

week	year	player_name	position	team	stadium	opponent_name	fd points
4	2019	Mariota, Marcus	QB	ten	a	atl	23.28
4	2019	Woods, Robert	WR	ten	h	atl	22.9
4	2019	Brown, A.J.	WR	ten	a	atl	22.9
5	2019	Cousins, Kirk	QB	min	h	den	22.86
5	2019	Cook, Dalvin	RB	min	a	den	22.8
14	2019	Hopkins, DeAndre	WR	hou	h	den	22.7

One thing to note is that there could be more than 1 matching player (same week + same team + has more than 20 fd points), which may change whether or not this is possible. I tried the solutions posted but could not build the table using those measures.

[amitchandak · Accepted solution]

@sedric1 , Not very clear. You can have table Like

filter(Summarize(Table,Table[week], Table[Year], Table[Player Name], Table[Position], Table[Team], Table[Stadium], Table[Opponent Name], "Fd Points" , Sum(Table[Fd Points])), [Fd Points] >20)

Or measure, That you can use with all other column

Sumx(filter(Summarize(Table,Table[week], Table[Year], Table[Player Name], Table[Position], Table[Team], Table[Stadium], Table[Opponent Name], "Fd Points" , Sum(Table[Fd Points])), [Fd Points] >20),[Fd Points])

If you want you can take Min and Max of Fd Points or any other column as per need

[sedric1]

@amitchandak  and @Greg_Deckler My reply to this post seems to have disappeared again so reposting it (and hoping they dont all show up later!) but i did want to mention that the summarize table function got me most of the way to my goal. The downside is that I have to filter it and sort it to see the groupings I want.

New Table = filter(Summarize('2019fdnfl','2019fdnfl'[week], '2019fdnfl'[Year], '2019fdnfl'[player_name], '2019fdnfl'[Position], '2019fdnfl'[Team], '2019fdnfl'[Stadium], '2019fdnfl'[opponent_name], "Fd Points" , Sum('2019fdnfl'[Fd Points]),"Stack Size", CALCULATE(COUNTROWS('2019fdnfl'),ALL('2019fdnfl'),'2019fdnfl'[team] = EARLIER('2019fdnfl'[team]),'2019fdnfl'[week]=EARLIER('2019fdnfl'[week]),'2019fdnfl'[fd points]>20)),[Fd Points]>20)

This now produces the grouping when I add a filter and sort to a table visual:

The one final question I have is whether or not I can consolidate this once again, so instead of 1 row per player, I add a new column to concatenate those player names in the group (and sum up their fd scores as well). I'm not quite sure how to modify the given query, but assume I may need to sumarize the summarized table to turn the results into this?

week	player_name	team	position	fd points	stack size
1	fitzgerald,larry;david johnson; murray,Kyler	ari	wr;rb;qb	66.62	3

[Greg_Deckler]

@sedric1 - You could use SUMMARIZE again along with CONCATENATEX to concatenate your column values. Might want to use GROUPBY instead of SUMMARIZE, depends.