Filtering report using USERELATIONSHIP measures with Charts

[rfinguerweg]

Hi there,

I have created a report using 2 tables, one of Orders Created and other one of Open Orders.

I want to show 2 charts, one of orders created by date of creation (Orders Created) and other chart showing open order by delivery date  (Open Orders).

I have created a calendar table, as below:

And I have linked the tables, as below:

I have created the first chart of orders created by creation date, using the formula:

Orders_Created = CALCULATE(
                            DISTINCTCOUNT(Orders Created[Doc.compra]), 
                            USERELATIONSHIP(calendar_table[Date], Orders Created[Creation Date]))

And the second one of Open Orders by Delivery Date:

Open Orders By Delivery = CALCULATE(
                                DISTINCTCOUNT('Open Orders'[Doc.compra]), 
                                USERELATIONSHIP(calendar_table[Date], 'Open Orders'[Delivery Date]))

Here is the problem:

I created a table to show orders details, such Creation Date and Delivery Date. For this one, I created 2 columns "Document Date" and "Delivery_Date":

Document Date = CALCULATE(
                        FIRSTNONBLANK('Open Orders'[Creation Date],1), 
                        USERELATIONSHIP(calendar_table[Date], 'Open Orders'[Creation Date]))

Delivery_date = CALCULATE(
                    FIRSTNONBLANK('Open Orders'[Delivery Date],1),
                    USERELATIONSHIP(calendar_table[Date], 'Open Orders'[Delivery Date]))

 The problem is, when I filter the first chart, Orders Created by Creation Date, the table filters both measures, and I have already tried using CROSSFILTER but didn't manage to make it work.

Any solution? I don't know what I am doing wrong. I want the first chart to filter ONLY "Document Date" and show the info "Delivery_date" and the second chart filter ONLY "Delivery_date" and show the info "Document Date".

[v-nuoc-msft]

Hi @rfinguerweg 

Can you tell me if your problem is solved? If yes, please accept it as solution.

Regards,

Nono Chen

[rfinguerweg]

Sorry, I deleted my calendar table and used native dates from my database. I don't know why using calendar table is such a pain... I don't even know why I should use calendar table... well, thanks anyway!!

[v-nuoc-msft]

Hi @rfinguerweg 

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.

[rfinguerweg]

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?

[v-nuoc-msft]

Hi @rfinguerweg 

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.

[Daniel29195]

@rfinguerweg 

in your case you need to have 2 seperate date tables. and each table connects to each column

and each visual reads from its respective datetable 

date table _document 

date table_delivery

or simpler approach L 
in the bar chart visuals, for the first visual ( document date) , read its date from the fact_table[document_date]

and for the second bar chart visual read (delivery_date) from the fact_table[delivery_date] 

instead of the dimdate table .

let me know if this works for you .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠