Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
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!
@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.
Proud to be a Super User! |
|
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
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
15 | |
11 | |
7 |
User | Count |
---|---|
25 | |
24 | |
12 | |
12 | |
11 |