cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Using 1 slicer to get all the correct dates

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 ?

Rbie

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

Anonymous
Not applicable

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors