Forum Discussion
Dates Colum left Merge
Hi all
I really hope someone can help please
I have 1 x dataset with 4 date columns each relating to something different
fulfilled date 1
fulfilled date 2
fulfilled date 3
fulfilled date 4
What I would like to do is create one slicer that fits all. So they can select year in one slicer , month in another and the actual date in another
So if they select April 2025 in the date slicer it would return calculations in the visual results for each of those columns that have that date in it. So is that date in date 1 or is it in date 2 or in date 3 or date 4. At the moment its doing something like an inner join as i'm grouping them on the fulfilled date 1
I've tried creating additional date only datasets and creating relationships, but there is ambiguous join issues when doing that
Does anyone know how this could work please.
Any help would be really appreciated
Kind regars
Karen
Hi KarenFingerhut ,
You have two options you can go with:
Option 1: Create inactive relationships and calculate against those dates. You can trigger the relationship by adding the function USERELATIONSHIP to your measure(s). Not scalable but something to consider.Option 2: Unpivot your columns to go "long" rather than "wide". Then connect your date table to your single one date column. Most preferred method.
Here's an example of how each would look like with their relationships:
7 Replies
- Unpivot your 4 date columns into one column using Power Query.
- Create a Date table with Year, Month, and Date columns.
- Link the unified date column to the Date table.
- Use slicers from the Date table to filter visuals.
- This setup lets you filter by any date across all 4 columns—cleanly and scalably.
Several approaches.
- You can use a purely active and a single direction many-to-many relationships (requries some data manipulation)
- Or use an active and inactive relationships and use measures to show the visible rows.
-
Please see the attached pbix.
Hi KarenFingerhut ,
You have two options you can go with:
Option 1: Create inactive relationships and calculate against those dates. You can trigger the relationship by adding the function USERELATIONSHIP to your measure(s). Not scalable but something to consider.Option 2: Unpivot your columns to go "long" rather than "wide". Then connect your date table to your single one date column. Most preferred method.
Here's an example of how each would look like with their relationships:
Hi hnguy71
Option 1 worked a treat, I then created USERRELATIONSHIP measures for the visual
Thanks very much 😁
Kind regards
Karen
you're welcome!
Wow thank you hnguy71 danextian Shahid12523 for you replies. I'll read through them now and see which option fits best. I'll keep you posted
Appreciate all your speedy responses😁
Kind regards
Karen
Hi KarenFingerhut ,
We’d like to check if you were able to go through the community team’s response to your issue. Please let us know if you need further clarification we’ll do our best to support you.
