The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello all,
TLDR---
Situation: Tables with *:1 relationship to a calendar that contains week-ending dates.
Goal: Add a visual toggle or list that allows the user to choose between Sunday and Wednesday week-ending days.
Nice to have: no Many to Many, no doubling the calendar table or increasing the size beyond 1-2 columns.
In a nutshell, I have created a powerbi report for us to track a project. The customer wants weekly snapshots of the report sent to them. The issue is that our weekending date is a Sunday and theirs is a Wednesday. Ideally, I would like to have a toggle or list slicer to select your week-ending day of the week so we can switch between Sunday and Wednesday instantly and it affects every visual of the report.
My setup is very straightforward: several queries, many dimensions, and one calendar table with dates and calculated WE dates.
All tables with dates have been replaced with the calendar index.
A few ideas I had that haven't worked:
1. Add a column to the calendar table after the query that calculates the WE date referencing a parameter (does not work since columns are calculated one time)
2. Attempted to create a measure but this was not possible with my skill set.
What could work but is clunky and not as agile/light:
1. Append the calendar with itself after updating the weekending date formula then add a column that is Sunday or Wednesday and use a filter to choose which week-ending date. The issue is this would create a many-to-many relationship which I prefer to avoid at all costs.
If anyone knows a fancy, agile way to make this work, please let me know!
Solved! Go to Solution.
I think you can use field paramters for this: https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters
the idea is that you have 2 columns in the date table; one with the Sunday end and one with the Wednesday end - which you can toggle between in the slicer with the field parameter.
I think you can use field paramters for this: https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters
the idea is that you have 2 columns in the date table; one with the Sunday end and one with the Wednesday end - which you can toggle between in the slicer with the field parameter.
Thank you for this! this even works with measures!
User | Count |
---|---|
75 | |
70 | |
39 | |
30 | |
28 |
User | Count |
---|---|
104 | |
95 | |
51 | |
48 | |
46 |