Joining two columns?

[HELPMEo_o]

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 PlayerID; labelling them XAxis and YAxis; and then dragging them into their own respective slicer.

If I create a measure that = VAR sX = SELECTEDVALUE(Xaxis[a]) RETURN sX, (or sY) it works perfectly and updates dynamically with whichever button on the slicer I am clicking.

At this point I have a unique name that defines the x-axis saved with var sX and y-axis saved with sY.

My plan is to make two more measures, each a switch command that toggles between the right column of data depending on the set sX or sY variable name. However, I get an error saying that multiplie values are supplied when a single one was expected after I try to graph the two columns.

VALUES() works whenever I drag playerID into one of the axis' from the original table, but I can't get the two values to graph with eachother. I think this is because whenever I specify with values(), I end up breaking the relationship between the two column values. then it doesn't graph.

Is there a way to innerjoin between my sX values and my sY values, and then select col1 and col2 as x-axis and y-axis afterward?

Hopefully I'm being clear with my end goal, thank you so much for your help. An easy work around would be a parameter but then it only displays numbers instead of the selected axis; plus I like the button feel of slicers way better. 

how I get valuespseudo tablesexample showing index makes the values() workgraph doesn't work when sX and sY are together

[v-chenwuz-msft]

Hi @HELPMEo_o ,

You are making a rather serious mistake, measure cannot return multiple values.

I think you want to display the x and y axes dynamically. visual works by displaying all the data of the fields one by one in the visual, and if there are filters applied to this visual, for example if you have filtered the x axis with slicer, it will only display the parts that match the slicer selection. So the effect of displaying the visual dynamically is achieved.

So, you can create a measure let the x-axis's data or value do not match the condition.

measure =
IF(
    SELECTEDVALUE( 'table1'[X Axis] ) = SELECTEDVALUE( 'table2'[sXTest] ),
    1,
    BLANK()
)

Then add the measure to the filters pane on this visual. And set show items where is 1.

If you need more help, please share some sample data.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PaulOlding]

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')

)

[ValtteriN]

Hi,

Have you tried using MAX or SELECTEDVALUE for both axises? When I tested this with example data that seemed to work fine.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

[HELPMEo_o]

I might be confused on what you are saying, but I think I've tried and it doesn't seem to work. I ended up stuck on

= CALCULATE(VALUES('Chess Comparison Chart'[Rating]), KEEPFILTERS('Chess Comparison Chart'[Website] = [Selected X Axis]))
and similar for my Y axis, 

But I get an error for using placeholder in a table function. So frustrating. 

Realistically, I'd like 2 measures that each gets 2 columns, rating and player id, then join on playerID into a calculated table and then just call the 2nd and 3rd column of the table as the x and y axis.

I understand that calculated columns don't update with slicers so I'm just stuck. Any ideas are appreciated.

[amitchandak]

@HELPMEo_o , In the measure you can not return table. values return table.

If you want dynamic axis

Axis Slicer
Dynamically change chart axis in Power BI
bookmark -https://blog.crossjoin.co.uk/2018/04/20/dynamically-changing-a-chart-axis-in-power-bi-using-bookmarks-and-buttons/
https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive
https://www.youtube.com/watch?v=6jeSIRpjv0M

if you want a measure slicer

measure slicer
https://www.youtube.com/watch?v=b9352Vxuj-M
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slic...