1 date slicer for 2 not joinable tables (>1 bil rows)

[Anonymous]

Hi,

I'm currently trying to have a date slicer that filter 2 dates variables in 2 tables on my visual. I want one slicer for user ergonomy.

The problem is :  both tables have more than billion rows and if I join them it creates an ambuiguity loop.

All data in my report come from Power Query.

Let's note Tab1[date1] the first table with column date1 and the same thing for Tab2[date2]

My attemp:

I've tried to create one slicer based on Tab1[date1] and from that get the selected min max range dates thanks measures to select only rows in my Tab2 that have Tab2[date2] in [min;max].

Here the measures I've used for that : 

min_selected_date1 = CALCULATE ( MIN ( Tab1[date1] ), ALLSELECTED (Tab1[date1] ))

And the same thing with max. I know this is working because I checked this in a table.

But the measure  that select the rows in Tab2 based on min max of selected dates in Tab1 doesn't seem to work, ofc I fitler tab2_selection is 1: 

tab2_selection = IF(OR(FIRSTDATE(Tab2[date2]) > [max_selected_date1], LASTDATE(Tab2[date2]) < [min_selected_date1]),0,1)

Thanks for your help,

[BA_Pete]

Hi @Anonymous ,

You should create a calendar table then relate calendar[date] to both Tab1[Date1] and Tab2[Date2]. You can then use calendar[date] in your slicer and both tables will be filtered at the same time.

I won't go into details here about how to create and relate a calendar table as there's millions of blogs about it online. Just know that this is 'the way it's done'.

Pete

[Anonymous]

Hi Ba_Pete,

Thanks for you answer !

I actually tried to do it but "relate" my calendar to Tab1[date1] AND Tab2[date2] created me the same problem of ambiguity loop so that why I stopped tried this way. And also the fact that I need all the records of date1 and date2

Also date1 and date2 type is date/time not only date

Maybe I'm not understanding the relation to create. Here a schema of my problem

[BA_Pete · Accepted solution]

You should create new date-only fields in each of your tables to relate to, and ensure they are both 'Date' data type. I'm also assuming that your date[date] column is Date data type.

You can do this in Power Query by creating a new column in each fact table as follows:

dateOnly =
Date.From([Date1])

I can't see any other reason why this would create an ambiguous relationship.

If this still causes an issue, please provide a screenshot of the error.

Pete

[Anonymous]

It worked, I was not understanding well the relations to create between the calendar and tab1/tab2

Thank you very much Pete !