Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a fact table with 3 date columns, e.g.
I want to create 3 slicers for each of the 3 date columns for filtering. For example:
Should I create 3 date tables for joining with the 3 different date columns from the Fact table? Or is there an easy way to achieve the purpose?
Solved! Go to Solution.
Hello @powerbiasker
Three copies of the data table will be the way to go I think. If you have a date table already you can make a copy of it in DAX simply by going to Modeling > New Table and entering the code something like:
Ship Date Table = DateTable
After that you can rename the columns in the copy of the table so instead of 'Year' is it 'Ship Year' but you don't need to.
There are two ways for you refer to:
1. Create 3 date tables for joining with the 3 different date columns from the Fact table as you said, you could copy the date table twice as jdbuchanan71 said to get three date tables.
2. Unpivot the fact table then use only one date table, selected order-date column, ship-date column and delivery-date column, then unpivot these columns and use [Attribute] as a slicer and [Value] column to connect with date table.
https://radacad.com/pivot-and-unpivot-with-power-bi
Regards,
Lin
There are two ways for you refer to:
1. Create 3 date tables for joining with the 3 different date columns from the Fact table as you said, you could copy the date table twice as jdbuchanan71 said to get three date tables.
2. Unpivot the fact table then use only one date table, selected order-date column, ship-date column and delivery-date column, then unpivot these columns and use [Attribute] as a slicer and [Value] column to connect with date table.
https://radacad.com/pivot-and-unpivot-with-power-bi
Regards,
Lin
Hi,
From what i understand, if you don't have the need to create a date dimension, you could use the 'date hierarchy' directly to filter for years.
I can't directly use the 3 dates from the fact table for the 3 slicers; otherwise, an error is gonna pop up like: ...expects a contiguous selection when the date column is not unique...
Got you! Thank you 🙂
Hello @powerbiasker
Three copies of the data table will be the way to go I think. If you have a date table already you can make a copy of it in DAX simply by going to Modeling > New Table and entering the code something like:
Ship Date Table = DateTable
After that you can rename the columns in the copy of the table so instead of 'Year' is it 'Ship Year' but you don't need to.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |