Comparing results from filtering 2 date range filters in the same Dataset

[CamelliaNguyen]

Hi everyone,

I am building up PBI report for my boss (if this task can't get done, I am afraid I would get fired 😅). There's a one dataset A which includes DATE column and ID column inside. The task is I have to build 2 slicers of date ranges using the same DATE column to filter the same ID column in the dataset A and showing a table of all IDs that remove all common IDs between 2 date ranges. I hope you all get what I mean.

My solution is to doubling the date table and the ID table then link the each date table with each ID table. But the problem is now I don't know how to compare the 2 tables to show the common IDs generated when filtering 2 date ranges. Please help me with this issue. If you have other better idea on how to work out it, please guide me through. Thousands thanks to you all!

[v-kkf-msft]

Hi @CamelliaNguyen ,

Please create the following inactive relationship.

Then create the measure to show the items in the visual when it is not blank.

Measure = 
VAR Date1_filter =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        USERELATIONSHIP ( 'Date 1'[Date], 'Table'[Date] )
    )
VAR Date2_filter =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        USERELATIONSHIP ( 'Date 2'[Date], 'Table'[Date] )
    )
RETURN
    IF ( Date1_filter + Date2_filter > 0, 1 )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CamelliaNguyen · Accepted solution]

Thank you @v-kkf-msft for your support, I have made my own solution. I guess you just don't get what I mean but it's alright. I duplicated the main table twice then use the date in both date in those tables for making slicers. So they don't conflict to each other when filtering. I use one table with unique ID to filter the other 2 duplicated tables. 

And the solution loooks like that

[CamelliaNguyen]

Hi tamej1, appreciate your support but it doesn't work the way I want it to be mate. I mean Im still unable to combine 2 tables after filtering 2 date ranges to show their different ID between them. 

[tamerj1]

Hi @CamelliaNguyen 
You can build a bridge table between the two fact tables that contains all unique ID's. Then use this table to filter both tables (One to many / One way) relationship. You can simply create a calculated table

ID's =
DISTINCT ( UNION ( VALUES ( Sheet1[ID1] ), VALUES ( Sheet2[ID2] ) ) )