Tables not filtering correctly the data

[TiagoBezerra]

Hey all,

I`m having a issue. I got a table that has 3 different dates (Start, Disconnected and Created)

I created a Date table with a active relationship with Created Date and inactive with Start and Disconnected.

After righting those 2 queries:

Sales Made = CALCULATE(DISTINCTCOUNT(customers[customerID]),USERELATIONSHIP('Date'[Date],customers[customerProspectToCustomerDate]))

Total Disconnect = CALCULATE(DISTINCTCOUNT(customers[customerID]),USERELATIONSHIP('Date'[Date],customers[Disconnected Date]),customerStatuses[customerStatusDesc]="Disconnected")

It seems to be doing the right job but as I filter the data choosing specific dates, it`s actually filtering based on created on (my active relationship) so the counts are not showing right.

My goal it`s to have some visuals and tables that if I filter by a date, it`ll show me (in separate tables) the created on, sold and disconnected.

This is the example I have currently:

Thank you all in advance for the help.

[Anonymous · Accepted solution]

Hi @TiagoBezerra

Your issue arises from the fact that Power BI, by default, filters based on the active relationship in your data model. Since your active relationship is with the 'Created Date', filtering by date in your report will primarily affect visuals that are related to this date. However, for visuals related to 'Start Date' and 'Disconnected Date', you need to use DAX to leverage the inactive relationships.

The USERELATIONSHIP function in your DAX queries is correctly used to activate these inactive relationships for specific calculations. However, if you want your visuals to respond to date filters based on these inactive relationships, you need to modify your approach slightly.

Please try the following:

Create Separate Measures for Each Relationship:You've already done this with your Sales Made and Total Disconnect measures.

When you select dates, use a slicer or the filter pane on your report page. This will allow you to control which visuals are affected by the date selection.

Edit Interaction Between Slicer and Visuals:

For each visual, you can control how it interacts with the slicers on your page.

Click on your date slicer, then click on the visual you want to modify.

Go to the "Format" tab in the Visualizations pane, then under "Edit Interactions," choose how the slicer affects the visual. You can opt to have the slicer not affect certain visuals if needed.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TiagoBezerra]

Thank you for helping with this. I actually have a slice with the date but it`s filtering the wrong column as I mentioned. I used your idea to get a work around and created 3 sync slicers for each date, filtered which table I want it to affect and hidden the other slices.