Reply
HarrisonBi
Frequent Visitor
Partially syndicated - Outbound

How to filter visuals correctly when using multiple dates

Hi,

 

I would like to know how (if possible) I can filter visuals correctly when I am using multiple dates. I will explain my situation below:

 

I have a date table and a sales table. These two tables are related by Date[Date] and Sales[InvoiceDate] as I usually report sales based on the invoice date and this has worked fine. 

 

However, on the sales table there is also another date called CreationDate, I would now like to see all my sales broken down by the creation date. So what I have done is, created an inactive relationship between Date[Date] and Sales[CreationDate] and created a measure 

CALCULATE(Sales[SalesTotal],

USERELATIONSHIP('Date'[Date],Sales[CreationDate]))

This works and I am able to use various columns from the date table (i.e. Financial Year) and see my sales according to the CreationDate - I have this displayed in a matrix.
 
However the problem I am facing is - if I click a month in the matrix, all the other visuals on the page are filtering the results based on the InvoiceDate NOT the CreationDate. I believe this is because the active relationship between the two tables is based on InvoiceDate.
 
How can I make the other visuals filter based on CreationDate when clicking a month in my matrix ?
 
Thanks
2 REPLIES 2
Ashish_Mathur
Super User
Super User

Syndicated - Outbound

Hi,

I think you will have to rewrite all the measures with the USERELATIONSHIP() function.


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

Syndicated - Outbound

@HarrisonBi , When you filter months from the date table. Visual using USERELATIONSHIP('Date'[Date],Sales[CreationDate]) will filter on Creation date.

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)