How to use a date slicer to filter two visualizations of two different columns in the same table in PowerBI

Question

I have searched for this query and attempted to implement some solutions i've found but i've not been able to apply the solutions to my problem so i'm hoping you can help.

We have a table with data, including "created" and "submitted" date columns. I'm wanting to use one slicer to individually show how many results for items created on the selected date, and items submitted on the selected date.

Any help would be great.

Thanks in advance.

Answer 1

You will need a calendar table for this to work correctly as you will have to have a relationship between your data table and the calendar. Using this example

In the above table, there is Order and Shipping Date, with a quantity column.

The calendar table is connected by the data column to the Order and Shipping Date. As you can only have one active relationship between the table, one is set as inactive. In this case the Shipping Date is Inactive.

So if I select a filter of 07/01/2018 on the Order data it will not show the quantity of items shipped, just the items ordered as it will filter out those rows.

In the above image, the table has been filtered using the active relationship and it only shows Orders for the 7th and Shipped on the 12th as that is what is in the table (Visual on the right)

So you will need two measures, one using the active and one using the inactive. So you count the Orders as normal, and the Shipped with a relationship modifier:

Total Orders = SUM('TableName'[Quantity])
Total Shipped = CALCULATE(SUM(Billing[Quantity]), USERELATIONSHIP(Billing[Shipping Date], 'Calendar'[Date]))

USERELATIONSHIP forces the Total Shipped to ignore the filter context on active relationship and use the inactive one.

So it should return Shipped 366, Ordered 353 for the 7/01/2018. You'll need to use the calendar table in your visuals to drive the dates, not the dates in your data.