Can a date slicer with inactive relationship filter on table and return values with no aggregation?

[Anonymous]

Hi,

I have a DateTable and InfoTable (example below) where the 2 have an active relationship on StartDate and an inactive relationshiop on EndDate.

ID	StartDate	EndDate	Type
User1	2022-01-01	2022-01-01	a
User2	2022-01-01	2022-01-05	b
User3	2022-01-01	2022-01-05	c
User4	2022-01-05	2022-01-04	a
User5	2022-01-05	2022-01-07	b

I'd like to have a simple table visualization which interacts with a slicer. The slicer is coming from the Date table but using the inactive relationship of the DateTable.

I'm already using USERELATIONSHIP where possible, but I cannot get my head around in case of the ID column, where I'd like to return all the values with said criteria without any aggregation.

For example:

If I select 2022-01-05 on the slicer I'd like to see only User2 & User3 in my visual and not User4 or User5.

Is there a way to achieve this without duplicating the date table (which I would try to avoid) or making the active relationship inactive too (which I would also try to avoid) .

Thanks in advance!

[Anonymous]

Thanks @SivaMani ,

I've tried this previously and the issue is that it only returns one small part of the data and not all the rows. Any idea why?

thanks

[SivaMani]

@Anonymous,

If I select 2022-01-05 on the slicer I'd like to see only User2 & User3 in my visual and not User4 or User5.

The proposed approach will meet the above requirement. Verify the other filters and make sure no conflicts. 

[Anonymous]

You are right, it had a conflict with another filter. Thanks agani!

[SivaMani · Accepted solution]

@Anonymous , Create a measure,

Count of Id = Calculate(COUNT(Table[Id]), USERLATIONSHIP(Date[Date], Table[EndDate]))

Add this measure as a visual level filter in the table visual and Apply Count of Id >= 1.