Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
User | Count |
---|---|
54 | |
22 | |
19 | |
16 | |
11 |
User | Count |
---|---|
82 | |
55 | |
40 | |
20 | |
12 |