Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, please refer to below tables:
Table 1 shows employee No (EmpNo)
Table 2 shows employee details with performance rating and year
Table 1 and table 2 relationship is by EmpNo
Filtered = Year 2020
Result is table 3
Showing employee no, performance rating (based on filtered year -1 year)
Regards,
FILTER YEAR | 2020 | ||||||||||||||||
Table 1 | RESULT USING MEASURE | ||||||||||||||||
EmpNo | Name | Grade | YEAR HIRE | EmpNo | Name | Grade | YEAR HIRE | Performance | |||||||||
1 | 2020 | 1 | E | ||||||||||||||
2 | 2016 | 2 | - | ||||||||||||||
3 | 3 | VG | |||||||||||||||
4 | 4 | - | |||||||||||||||
Table 2 | |||||||||||||||||
EmpNo | Name | Grade | Performance | Year | |||||||||||||
1 | E | 2019 | |||||||||||||||
1 | E | 2018 | |||||||||||||||
1 | VG | 2017 | |||||||||||||||
1 | G | 2016 | |||||||||||||||
2 | E | 2018 | |||||||||||||||
2 | E | 2017 | |||||||||||||||
2 | G | 2016 | |||||||||||||||
3 | VG | 2019 | |||||||||||||||
3 | E | 2018 | |||||||||||||||
4 | POOR | 2020 | |||||||||||||||
Can you be a bit clearer on what you are trying to achieve?
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingDear edhans,
i have two tables:
Table 1 - data includes employee number, and other fields
Table 2 - data includes employee number, performance rating and year when rating happened
Table 3 - is the result of having employee number (from table 1) and performance rating based on year filter. Table 1 and table 2 relationship is field empno. both fields are present in Table 1 and 2.
Table 1 | |||||||
EmpNo | Name | Grade | YEAR HIRE | ||||
1 | 2020 | ||||||
2 | 2016 | ||||||
3 | |||||||
4 | |||||||
Table 2 | |||||||
EmpNo | Name | Grade | Performance | Year | |||
1 | E | 2019 | |||||
1 | E | 2018 | |||||
1 | VG | 2017 | |||||
1 | G | 2016 | |||||
2 | E | 2018 | |||||
2 | E | 2017 | |||||
2 | G | 2016 | |||||
3 | VG | 2019 | |||||
3 | E | 2018 | |||||
4 | POOR | 2020 | |||||
RESULT WHEN FILTERED BY YEAR | |||||||
FILTER YEAR | 2020 | ||||||
RESULT USING MEASURE | |||||||
EmpNo | Name | Grade | YEAR HIRE | Performance | |||
1 | E | ||||||
2 | - | ||||||
3 | VG | ||||||
4 | - | ||||||
You may refer to the measure below.
Measure =
MAXX (
FILTER (
Table2,
Table2[Year]
= SELECTEDVALUE ( 'Calendar'[Year] ) - 1
),
Table2[Performance]
) & ""
Thanks...but it is not working...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |