cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
yoav20007 Regular Visitor
Regular Visitor

filter tables by range of dates

I have only 2 tables and i want when i filter the first table it will filter other table between dates that in the first table.

i conect then with many to many relationship (maybe my mistake?) .

if it is posible to create a new table from table 2 just with the row in the rage dates that i select from table 1?

 

table2.PNG

4 REPLIES 4
Highlighted
Super User
Super User

Re: filter tables by dates

Here is a lot to learn when you start out. You should not creat this many to many relationship (there are exceptions, but this is not one of them).  You need another table containing all the uniqueness values containing both columns that you are joining on, and join both to that. Read my article here

https://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/

 

Then this one

https://exceleratorbi.com.au/power-pivot-calendar-tables/

 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
yoav20007 Regular Visitor
Regular Visitor

Re: filter tables by dates

Thanks 

I try to connect them to 3 table that have unique values, but i still have a problem to filter by date from the first table.

 

yoav20007 Regular Visitor
Regular Visitor

Re: filter tables by dates

now i can do it manually . but how can i creat new table with the results automaticly?  

table4.PNGtable3.PNG

Super User
Super User

Re: filter tables by range of dates

Hi,

Go to Data > Modelling > New Table and write this formula

=CALENDAR(MIN(Data[Planting Date]),MAX(Data[Harvest Date]))

Now build a relationship from the Date column of Table1 and Table2 to the Date column of the Calendar Table.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 248 members 2,450 guests
Please welcome our newest community members: