Forum Discussion

HELPMEo_o's avatar
HELPMEo_o
Frequent Visitor
4 years ago

Joining two columns?

I have a dataset with a bunch of columns that list out ratings from 0 to 3000 down the rows, with playerID as the first column. I created 2 pseudo table that holds all of the column names other than...
PaulOlding's avatar
PaulOlding
Icon for Solution Sage rankSolution Sage
4 years ago

Hi HELPMEo_o 

I don't know much about Chess, are the x & y axis values different game types?  For this answer I'll assume they are.

The first thing I would do is unpivot the data in Power Query so instead of having a table with different ratings in columns, we'll have the ratings on different rows. The aim is to get a table with 3 columns:  playerID, game type, rating.

The next part you've already done - having 2 tables with the game types in them.  Now create a relationship from those 2 tables to the main table on game type.  They should be 1 to many.

Now for the measures.  With 2 tables that filter the same column in the main table (game type) we need our measures to ignore one of them.  Ie for the x-axis measure we need to ignore the y-axis table, and vice versa.

x-measure =
CALCULATE(

     AVERAGE('main table'[rating]),

     REMOVEFILTERS('y-axis table')

)