Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello experts,
We have two tables coming from multiple MS SQL Server sources located at different locations. The data is as below:
EmpLocation | |||
EmpId | Location | Active From | Active Till |
1 | A | 01-01-2019 | 01-02-2019 |
1 | B | 02-02-2019 | 01-03-2019 |
EmpReporting
|
I want to form a relationship between the tables using Employee Id and dates. As it is visible that the dates are not an exact match.
Consider the cells having Orange colored values. Here the EmpId 1 was active at Location A from 01-Jan-19 till 01-Feb-19. But the reporting that he/she has done starts at 31st-Dec-2018 and ends at 28-01-2019. The requirement is to show how much was spent by an employee on each location.
Also, i need the details from the second table when the data point in the first table is selected.
Note that the Report From(EmpReporting) date could be less/greater than or equal to the Active From Date(EmpLocation Table). Similar scenario is with the Report till and Active till dates.
Require your help in figuring out how the relations would work as per the given scenario in Power BI.
Regards,
Chetan
Solved! Go to Solution.
Hi @Anonymous ,
Create a measure as below and add the measure in a table visual.
Measure = CALCULATE ( MAX ( EmpLocation[Location] ), FILTER ( EmpLocation, 'EmpLocation'[Active From] <= MAX ( EmpReporting[Report Till] ) && EmpLocation[Active Till] >= MAX ( EmpReporting[Report Till] ) ) )
Pbix as attached.
Hi @Anonymous ,
Create a measure as below and add the measure in a table visual.
Measure = CALCULATE ( MAX ( EmpLocation[Location] ), FILTER ( EmpLocation, 'EmpLocation'[Active From] <= MAX ( EmpReporting[Report Till] ) && EmpLocation[Active Till] >= MAX ( EmpReporting[Report Till] ) ) )
Pbix as attached.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |