2 columns with same kind of data - grouping problem

[Farau]

Hello community. It is my first post here and I'm quite a new user to Power BI. I had some experience with MicroStrategy and Tableau before. Right now I create dynamic report for my own practising and learning purposes. I've done something similar in Tableau before, but I've difficulties recreating that in Power BI.

Objective: I want to create report which filters and shows results of 2 player games between various factions.

1) I have connected to data from google sheet, where people type results of games in board game. Then done some basic cleansing in power query to exclude data I'm not interested in (3/4 player games). The data looks like this:

2) In my Tableau report I've created a group called "Factions combined". It covered every possible combination of factions no matter if it was played as Player 1 or Player 2.

3) This allowed me to use this "Factions combined" group to do charts like this:

My main question is - what should I use to combine Player 1 and Player 2 factions so they are treated as one and can be checked against each other no matter the column faction is in? I've tried hierarchy and grouping but no results so far. Also I've been trying some groupby and summarize in DAX, but also can't get it working.

Cheers, Rafael.

[Farau · Accepted solution]

Hi @Greg_Deckler. I've came with solution - I've created a new column which added the names of two factions that played (from total 4) so I've ended up with 6 various match-ups.

I used this as my slicer for dashboard to filter the data only for given match-up in some visuals. I present results below.

Thank you for engagement, the problem is solved!

Cheers, Rafael.

[Greg_Deckler]

@Farau You could generate a similar table to what you did in Tableau using the GENERATE function to generate all possible value combinations between two columns. Something like:

Table =
  GENERATE(
    SELECTCOLUMNS('Table',"Player1",[Player 1 faction],
    SELECTCOLUMNS('Table',"Player2",[Player 2 faction]
  )

[Farau]

Hello @Greg_Deckler, thank you for your time to put this answer down.

I've tried your suggestion and it produces a table with over 44k records, single combination being multiplied in few thousand rows. Not exactly what I need, although I might don't know how to use it properly.

I modified query and created 4 conditional columns for each of the faction. If faction appeared in given game its name will be copied into dedicated column. I'll try to work with hierarchy and grouping again from that point.

If you have any more ideas how to achieve result I've posted in 1st post (in Tableau) - you are welcome!

Thank you for effort, Rafael.

[Greg_Deckler]

@Farau I would need sample data as text or a PBIX/Tableau file to work with as I am still not certain exactly what you are trying to achieve or fully understand where you are running into issues.