Using 1 slicer to get all the correct dates

[Rbie]

I have the following 3 tables in my model , table 1 has a one to many relation to table 2 and 3 via EmplID

EmplID	EmployedFrom	EmployedTo
1	1/01/2019	31/12/2054
EmplID	ReportToFrom	ReportsToTo	ReportsToID
1	1/01/2019	31/12/2019	1
1	1/01/2020	31/12/2020	3
1	1/01/2021	31/12/2054	4
EmplID	PositionFrom	PositionTo	PositionID
1	1/01/2019	30/06/2019	12
1	1/07/2019	30/06/2021	13
1	1/07/2021	31/12/2054	14

What I'm trying to achieve via 1 slicer is to get the correct record out of each table. So for instance if I would choose the date 

1/1/2020

The report  would show 

EmplID	Reportsto	Position
1	3	13

1/1/2021 would result in

EmplID	Reportsto	Position
1	4	13

How can this be achived ?

Thanks in advance ,

Rbie

[Anonymous · Accepted solution]

Hi @Rbie ,

Please try to add a Date table and create measure with below dax formula:

Date:

EmpID =
VAR _a =
    SELECTEDVALUE ( 'Date'[Date] )
VAR _b =
    CALCULATE (
        MAX ( 'Table 1'[EmplID] ),
        FILTER ( ALL ( 'Table 1' ), [EmployedFrom] <= _a && [EmployedTo] >= _a )
    )
RETURN
    _b

Reports To =
VAR _a =
    SELECTEDVALUE ( 'Date'[Date] )
VAR _b =
    CALCULATE (
        MAX ( 'Table 2'[ReportsToID] ),
        FILTER ( ALL ( 'Table 2' ), [ReportToFrom] <= _a && [ReportsToTo] >= _a )
    )
RETURN
    _b

Position =
VAR _a =
    SELECTEDVALUE ( 'Date'[Date] )
VAR _b =
    CALCULATE (
        MAX ( 'Table 3'[PositionID] ),
        FILTER ( ALL ( 'Table 3' ), [PositionFrom] <= _a && [PositionTo] >= _a )
    )
RETURN
    _b

Add a slicer with Date table, add a table visual with measure:

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.