Filter table rows by Date Table , where Main Table contains dates in 5 columns and can be duplicate

[Anonymous]

Hi users, 

I need to filter my table as per selected date or date range from Date table column (refer left side of the image) and I need to filter weather the range of those dates contains in my 5 columns to get the ChosenSeat falls in that date range. 

I'm trying to messing around, but can't get it working. Any help would be appreciated. 

Thanks

[Anonymous]

Hi @Anonymous,

I'd like to suggest you unpivot your date fields then you cna simply filter across raw date fields.

Unpivot columns (Power Query) 

Regards,

Xiaoxin Sheng

[Anonymous]

@Anonymous , tried it and it throws an error to all the Table values. Also tried with changing the data type of these columns.

[Anonymous · Accepted solution]

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

[Anonymous]

@Anonymous Seems, like it worked. Thanks , I didn't thaught of duplicating it , and not sure how its worked. 

If you can explain on how its worked with duplicating table, and what will happen if I delete the previous table, which is not in use for any visuals ?

Thanks again, marked that as a solution for this Issue. 

[amitchandak]

@Anonymous , Create a date table join with all dates. One active, all other inactive. Create a formula to count based on all dates using userelation.

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

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.

[Anonymous]

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? 

[Anonymous]

@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

[Anonymous]

@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. 

[Anonymous]

Ahh , got my post unmarked as spam, Autopost spam filteration need imporvement.