topic Re: 3 Table Relationships in DAX Commands and Tips

3 Table Relationships

KW123 — Wed, 05 Feb 2025 06:24:30 GMT

Hi there,

I am building a report which has the following tables:
-Dates
-Senior Employee goals

-Middle Employee goals
-Entry Employee goals
-Employee list

In my visualization, I have three tables. One for each type of employee (Senior, Mid, Entry) I have a slicer that will work on all three table visualizations and it slices by store number. I would like the report to only show employees in the store number selected.

I originally had all three goal tables with a relationship to my dates table but in the visualization, it was showing ALL employees ignoring the store number slicer. Each employee row had the correct MTD goal however. If I do not have a MTD measure

CALCULATE (

SUM ( 'Senior Goals'[Goal 1]),

FILTER (

ALLSELECTED ( 'Dates'),

[Date]

IN FILTERS ( 'Dates'[Date] )

))

then each employee row only shows the goal not in MTD format. But, the slicer works and it will only show me employees for the selected store.

So I decided to add the Senior employee goal to the employee list (relationship on employee title) and that worked - the visualization only showed the selected store and the goal with my goal MTD measure.

However, when I try to add the Mid and Entry tables to the employee list, it is saying that there is ambiguity....

My question is this, Is there a way to make that slicer work by store number and date (I need my goal to show as a MTD) or is there a way to have all three goal tables link up to both the dates and the employee list.

Thanks in advance!

Re: 3 Table Relationships

bhanu_gautam — Wed, 05 Feb 2025 10:21:19 GMT

KW123 Combine the Senior, Mid, and Entry Employee goals into a single table with an additional column to indicate the employee type (Senior, Mid, Entry). This will act as a bridge table.

Create relationships between the bridge table and the Employee list table on the employee ID or title.
Create relationships between the bridge table and the Dates table on the date field.

Use the bridge table in your visualizations instead of the individual goal tables. This will ensure that the slicer works correctly across all employee types.

Create DAX measures to calculate the MTD goals using the bridge table.

MTD Goal =
CALCULATE (
SUM ( 'BridgeTable'[Goal]),
DATESMTD ( 'Dates'[Date] )
)

Ensure that the slicer is set to filter by store number and is connected to the Employee list table. This will filter the bridge table through the relationships.

Re: 3 Table Relationships

KW123 — Wed, 05 Feb 2025 16:47:26 GMT

Hi bhanu_gautam

Thank you so much for replying to this!! I created a bridge table with the employee title as a column. I created a relationship to the employee table through the employee title, as well as to the dates table on date.

Now I am getting an error saying that there is ambiguity with my sales table which is housing day to day sales

Re: 3 Table Relationships

Anonymous — Mon, 10 Feb 2025 08:09:41 GMT

Hi KW123 ,

You did not provide your data model, so I tried to create several tables myself. However, because you involve too many tables, I am not sure whether the sample data I created is consistent with your actual situation. If it is not consistent or my solution is not helpful to you, please provide sample data in all your tables, thank you!
Here are my sample datas:

Here are the relationships:

Then use these DAXs to create measures:

Senior MTD Goal = CALCULATE ( SUM ( 'Senior Employee Goals'[Goal 1] ), FILTER ( ALLSELECTED ( 'Senior Employee Goals'[Date] ), 'Senior Employee Goals'[Date] <= MAX ( 'Dates'[Date] ) && MONTH ( 'Senior Employee Goals'[Date] ) = MONTH ( MAX ( 'Dates'[Date] ) ) && YEAR ( 'Senior Employee Goals'[Date] ) = YEAR ( MAX ( 'Dates'[Date] ) ) ) )

Mid MTD Goal = CALCULATE ( SUM ( 'Middle Employee Goals'[Goal 1] ), FILTER ( ALLSELECTED ( 'Middle Employee Goals'[Date] ), 'Middle Employee Goals'[Date] <= MAX ( 'Dates'[Date] ) && MONTH ( 'Middle Employee Goals'[Date] ) = MONTH ( MAX ( 'Dates'[Date] ) ) && YEAR ( 'Middle Employee Goals'[Date] ) = YEAR ( MAX ( 'Dates'[Date] ) ) ) )

Entry MTD Goal = CALCULATE ( SUM ( 'Entry Employee Goals'[Goal 1] ), FILTER ( ALLSELECTED ( 'Entry Employee Goals'[Date] ), 'Entry Employee Goals'[Date] <= MAX ( 'Dates'[Date] ) && MONTH ( 'Entry Employee Goals'[Date] ) = MONTH ( MAX ( 'Dates'[Date] ) ) && YEAR ( 'Entry Employee Goals'[Date] ) = YEAR ( MAX ( 'Dates'[Date] ) ) ) )

And the final output is as below:

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