Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
@jtrendl , 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
Ahh, this is exactly what I needed, thanks so much!!!!
@jtrendl , 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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
37 | |
25 | |
19 | |
14 | |
8 |
User | Count |
---|---|
68 | |
48 | |
45 | |
18 | |
15 |