Forum Discussion
Filtering report using USERELATIONSHIP measures with Charts
Hi Anonymous
My assumption is that you want to create two separate visualizations that are filtered independently: one for orders created by creation date and one for open orders created by delivery date. Additionally, you want to have a table that shows both creation and delivery dates without applying filters to the chart that affect both measures.
Here are my suggestions.
First, make sure your calendar is properly connected to both. You can do this by using a function to clear the filter context for a specific column. For example:
Document Date = CALCULATE(
FIRSTNONBLANK('Open Orders'[Creation Date],1),
ALLEXCEPT('Open Orders', 'Open Orders'[Doc.compra])
)
Delivery_date = CALCULATE(
FIRSTNONBLANK('Open Orders'[Delivery Date],1),
ALLEXCEPT('Open Orders', 'Open Orders'[Doc.compra])
)
These measures should retain the filter context but remove any other filters that may have come from the chart.
You appear to have correctly set up the measure values for activating the correct date relationships for each chart. If filtering one chart affects another chart, it may be because the filter context is propagated through the report. To prevent this, you can modify the visual interaction of the chart:
Select the first chart.
Go to the Format tab in the Visualizations pane.
Find and click "Edit Interactions".
You will see icons appear on other visuals, click the "None" icon on the visuals you do not want to be affected by the first chart.
Repeat these steps for the second chart, making sure to prevent it from filtering the first chart and table.
If you still have problems, it is best to provide the pbix file and be careful to delete sensitive data.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Now the charts are not filtering 'Document Date' or "Delivery_date", and I would like that both charts filters both. Is that possible using calendar table? Or should I just delete calendar table and use column dates from tables?
Hi Anonymous
It is indeed possible to filter both charts using a calendar table in Power BI, and there is no need to delete the calendar table. Using a calendar table is recommended as it allows date fields to be consistent throughout the model and simplifies time intelligence calculations.
Here's a step-by-step guide to help you set up filtering:
Make sure your calendar is set up correctly:
Make sure your calendar table contains all the dates you may need for the Document Date and Delivery Date fields.
Check if the calendar table is marked as a date table in the model.
Build relationships:Define the relationship between the calendar table and the table containing Document Date and Delivery Date. These should generally be one-to-many relationships, with one side on the calendar table.
Filter using calendar:In the report, add a slicer and use the date field from the calendar table as the slicer's field.
Configure the slicer to filter by an appropriate date range or selection.
Make sure cross filtering is enabled:If necessary, adjust the cross-filtering direction in the relationship to ensure that selecting dates in the slicer filters the data in both charts.
Verify filter propagation:Test the slicer to verify that selecting a date range updates both charts accordingly.
Model relationships in Power BI Desktop - Power BI | Microsoft Learn
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.