Forum Discussion
Helper IIINeed help creating a table visual based on matching criteria
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!
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
5 Replies
- amitchandak
Super User
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
sedric1amitchandak 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
Community Champion
sedric1 - You could use SUMMARIZE again along with CONCATENATEX to concatenate your column values. Might want to use GROUPBY instead of SUMMARIZE, depends.
- 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.
