Date Filter on Two Different Date Columns

Power BI Data Model
1

I currently have customer service case data which is in the format of:

Case Number Opened Date Resolved Date
1 1-Mar-22 3-Mar-22
2 3-Mar-22 15-Mar-22
3 28-Feb-22 30-Mar-22
4 31-Jan-22 29-Mar-22
5 3-Feb-22 1-Mar-22
6 4-Mar-22 10-Mar-22
7 5-Mar-22
8 3-Nov-21 3-Mar-22
9 3-Mar-22 15-Mar-22
10 28-Dec-21

I have connected my date table to both the resolved and opened date columns. In my report, there are some instances where I would like the Date filter to use the resolved date column, and some instances where it uses the opened date column. I am aware of the USERELATIONSHIP DAX function, however, there are no measures in the ‘Table’ visual that I am using. I simply want a list of case IDs.

My desired output is that in the same file, I can have one table that utilises the resolve date relationship and another that utilises the opened date.

I have attached a PBIX file for reference.

Your help is greatly appreciated.

Test.pbix (115.7 KB)

2

Hi @leontan.3,
If you have two date columns in the fact table and you need to filter the report by both dates using a slicer, you need to create two date tables with an active relationship for each of them and add a slicer to filter by open date and another to filter by resolution date.

twodatestable
twodatestable1327×697 21.5 KB

Here is a link that may help you.

Regards.

1 Like
3

Hi @leontan.3 , just brainstorming… Have you tried set up two relationships, set them inactive and design measure to filter the cases based on both date columns using that function, USERELATIONSHIP?
I wonder if this lecture can give you any ideas?

Otherwise, @jafernandezpuga 's suggestion looks robust and like a way to go. I just personally would prefer having only one date slicer.

2 Likes
4

Hi @StaceyL,
In this case, @leontan.3 tells us that there are no measures in the Table visual object and it needs a segmenter to be able to filter by the opening date or by the resolution date.

Regards,

5

my solution with similar issues has been to create a measure to use as filter - this does NOT have to be visible in the final output.

Create measure:

Using Opened Date = 
CALCULATE(
    COUNTROWS( Test ),
    USERELATIONSHIP( Test[Opened Date],'Date Table'[Date] ))

and add that to the table filters in the filter pane:
image

but do not add it to the values of the table visual:
image

updated example attached
eDNA solution - Filtering table with invisible Measure.pbix (116.9 KB)

4 Likes
6

It is great that more user-friendly solution is there! Thank you, @Heather , for sharing knowledge.

1 Like
7

Thanks everyone for their help. Both options are great but I’ve decided to go with Heather’s solution as it will be a bit simpler for the end user. Thanks again. :slight_smile:

1 Like
8

Hi @leontan.3 , just found a video that might be of interest. It pretty much supports the solution that Heather has offered.

1 Like