Single scatter chart w/ dynamic XY axis

[tejasruparel]

Hello,

I wanted to ask if there is way to have one single scatter chart w/ dynamic XY axis selection. Please see below:

 I'd appreciate if you could share any insights/tips/tricks/resources. Thanks!

[v-yuta-msft]

@tejasruparel ,

I'm confused on your description, could you please clarify the meaning of "Query1, 2, 3" and "Col1~5"? And which columns are the three slicers based on?

Regards,

Jimmy Tao

[tejasruparel]

Hey Jimmy,

So query1, 2 and 3 refer to 3 different query tables. 

and Col1 - 5 refer to columns within these three tables. 

see attached picture. 

[v-yuta-msft]

@tejasruparel ,

So does X-axis and Y-axis take the columns from two tables? Could you share more details?

Regards,

Jimmy Tao

[tejasruparel]

Hey Jimmy,

Please see attached pbix

My original plan was to be able to:

1. Select what Sections to compare (multi-select slicer for Section 1, Section 2 and Section 3)
2. Select what metric (column) to plot on x-axis (single select slicer for thickness, mass, intrusion etc.)
3. Select what metric (column) to plot on y-axis (single select slicer for thickness, mass, intrusion etc.)

So, the goal was to plot, for example - mass vs intrusion for Section 1 and Section 2

---

After spending some time trying to figure out a good approach I ended up transforming my data as follows:

1. Create a new source w/ all Sections combined and respective columns aligned, and Add a column to index each unique row
2. Duplicate this Table and unpivot all metric columns - Combined_unpivoted_X
3. Duplicate this Table and unpivot all metric columns - Combined_unpivoted_Y

Now I'm able to dynamically choose what sections to compare (using multi-select slicer) and what Xvalue and Yvalue to plot (using the single select slicer), but when I plot Xvalue vs Yvalue the scatter chart says 'Can't determine relationship between fields'.

I have tried defining relationships, but it still won't plot Xvalue vs Yvalue, and I'm not sure why!

In the attached file - you'll see I'm able to pull all the necessary data - just not able to plot it!

I'd appreciate if you could take a quick peek at it and let me know if I'm missing something. 

Thanks!!

[VasTg]

@tejasruparel 

Refer to this posts on how to dynamically change the attribute. Its not exactly what you need but the idea is same.

https://community.powerbi.com/t5/Desktop/Dynamic-change-in-X-Axis/td-p/86167

https://community.powerbi.com/t5/Desktop/Change-x-axis-categories-dynamically/m-p/280583

If it helps, mark it as a solution

Kudos are nice too