Forum Discussion
Filter table rows by Date Table , where Main Table contains dates in 5 columns and can be duplicate
Hi Anonymous,
I'd like to suggest you create a duplicate table and apply unpivot columns on the copied table, you can link this new table with raw tables and it does not affect the raw table relationships.
Regards,
Xiaoxin Sheng
Super UserAnonymous , Create a date table join with all dates. One active, all other inactive. Create a formula to count based on all dates using userelation.
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.
Thanks amitchandak, Links looks very good,
But I think those still doesn't clear and gives my answer. I could easily change by date from 'dd mmmm yyyy' to 'dd-mm-yyyy'.
But I still need to filter out my data as based on the date range. Its a simple filter but my main table does contain duplicate dates and in all 5 column and I need that. In the above shown image , for selected date range, if my data will filter out for right hand side ticked rows, that will be the answer to my current issue.
Hope it makes sense, what i'm trying to achieve. But if you response does contains the solution, then that probabely I didn't understand it well, i think 🤔.
- amitchandak
Anonymous ,Can you share sample data and sample output in table format?
amitchandak Sure you can downlaod a sample file from here Data.xlxs
Column Date1 to Date5 does contains the dates, If my filter date contains in any of these Date Columns (which is from Date1 to Date5), then I need to show the 'ChosenSeat' it in my table view.
filter date is just a Date Table.
Ahh , got my post unmarked as spam, Autopost spam filteration need imporvement.
amitchandak Apologies my shared excel link has been removed by Moderator.
Here a sample data how its looks like, This is my main table which I need to filter it out by date range (Slicer having Date Table).
and the column ( Date1 to Date5 ) should filter out when I select any date range which lies in any of these 5 columns.
Hope this make sense.
ChosenSeatDate1Date2Date3Date4Date5
Seat 6 12-07-2020 00:00 13-07-2020 00:00 Seat 10 29-07-2020 00:00 Seat 106 02-07-2020 00:00 03-07-2020 00:00 04-07-2020 00:00 05-07-2020 00:00 Seat 109 31-07-2020 00:00 01-08-2020 00:00 02-08-2020 00:00 03-08-2020 00:00 Seat 13 02-07-2020 00:00 03-07-2020 00:00 04-07-2020 00:00 Seat 13 06-07-2020 00:00 07-07-2020 00:00 Seat 15 04-07-2020 00:00 05-07-2020 00:00 06-07-2020 00:00 07-07-2020 00:00 Seat 17 02-07-2020 00:00 Seat 17 29-07-2020 00:00 Seat 19 29-07-2020 00:00 Seat 19 04-08-2020 00:00 Seat 21 02-07-2020 00:00 03-07-2020 00:00 Seat 23 03-07-2020 00:00 04-07-2020 00:00 05-07-2020 00:00 Seat 27 03-07-2020 00:00 04-07-2020 00:00 05-07-2020 00:00