How to overcome that you can't add a secondary X axis

[Anonymous]

Hi there,

I have a fact table with employee IDs and their salary data (named SALI Table). One ID can occur multiple times if the employee had multiple raises. 

CIAM ID	Year Start Base Salary	Current Base Salary
23	$800 000	$870 630
34	$654 654	$660 119
25	$546	$6 011
23	$870 630	$900 000
34	$660 119	$700 000

I've created a reference table (named UniqueID Table) from this fact table which contains the minimum value of the Year Start Base Salary and the Maximum of the Current Base Salary for each unique ID, and categorized each salary into custom categories based on given criteria:

CIAM ID	Year Start Base Salary	Current Base Salary	Year Start Base Salary Category	Current Base Salary Category
23	$800 000	$900 000	below 70%	below 70%
34	$654 654	$700 000	100-110%	120-130%
25	$546	$6 011	70-80%	above 150%

Now, my issue is, that I need to creat a plot, that shows the number of unique ID-s per custom salary category group (below 70%, 70-80%,...,above 150%) for the Year Start Base Salary Category and the Current Base Salary Category as well.

Something like this:

In order to make the axis labels in the right order, I needed to create a separate dimension table (named: RSPbins) like this:

However, I can't connect this dimension table (RSPbins) to both the Year Start Base Salary Category and Current Base Salary Category, because the UniqueID Table should be filterable by the original fact table (SALI Table) and that would create ambiguity between RSPbins and the SALI table (There can be ID-s that should be filtered based on Year Start Base Salary Category but not based on Current Base Salary Category). 

I can't create two separate dimension tables, because Power BI only allows to add a secondary axis to your Y-axis and not to the X-axis.

I hope I managed to explain the situation clearly.

I'm really stuck, any idea is really really appreciated how to overcome this problem.

[amitchandak · Accepted solution]

@Anonymous , Make sure you do not have bi-directional join. Then join both columns with same table one join will be inactive, which you can join using userelationship

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

[Anonymous]

Ahh, this is exactly what I needed, thanks so much!!!!