Multiple relationships with Date report filter

[Hind26]

Hi,

I have a Fact Table that contain two different Date (exple : Creation and Update) and i have Date Dimension, beside all this i have a Date Report filter that i did from my Date dimension and i want that this filter works on all my dates including the two date on my fact Table, since the multi relationships doesn't work on Power BI i really don't know how to do this.

I have more than two different date, that was just an exemple, actually i want just to make sure that everyone is looking at the same range within my fact table.

I hope i was clear, please let me know if i wasn't that i explain more.

Its is a very urgent subject i hope you could help me

Thank you

[Nick_Hebert]

I have a similar use case where I have effective dates and expiration dates.  I use 3 different date tables, one for Effective (active), one for expiration date (Inactive) and one main date table for filtering that has an active relationship to both of the other date tables.  I specify which relationship I want to use regarding the fact table to the sub date tables in each measure.  Then I use the main date table for all filters.  This filters each of the sub date tables appropriately.

I see that this post is old, but hope it helps someone.

[rajulshah]

You can make relationship on these two dates.One active and another one inactive.

You can create active relationship on the one that is used most while calculating a measure , and can use USERELATIONSHIP whenever you want to use other date.

Hope this helps,

Thanks!

[Hind26]

Thank you for your answer, i have already tried this solution, but i figured out that using the function UseRelationship on the inactive relationship inactivate the other one, it doesn't work in addition on my existing active relationship.

[rajulshah]

Sorry , but you cannot have more than one relationship between two tables.

[Hind26]

So there is any other possible way to filter on the Report Date filter and my two dates be automatically included in the range chosen by the user in the filter? 

[v-yulgu-msft]

Hi @Hind26,

It is not possible to create multiple relationships between two tables. You want to make both two date columns to be affected by the single Report filter, right? You could use MAX(Date Dimension[Dates]) to get the selected date value, then use this expression to filter records through a measure. In that case, you don't need to create any relationship between Fact Table and Date Dimension.

Best regards,

Yuliana Gu

[Hind26]

Hello v-yulgu-msft,

Thank you for your answer. and yes i want  my date columns in my fact table to be affected by the single Report filter.

I tried your proposition but the max (date Dimension(dates)) gives me the maximum value in the table Dimension not the maximum selected in the filter 

I even used the Function Allselected like : calculate(max( Dimension[DATE]);ALLselected(Dimension)) but it doesn't give the max selected in the filter. 

The client requires this functionality and i don't know how to do it

[v-yulgu-msft]

Hi @Hind26,

Did you add the dimension[dates] into report level filters? Did you remove the the relationship between fact table and dimension table? In my test, I could get the selected date using max (date Dimension(dates)). Please try whether select date = SELECTEDVALUE(Dim[Date]) works.

Regards,

Yuliana Gu

[Hind26]

Any proposition pleaaase 😞