Forum Discussion
DAX Help
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 | |||||||||||||||
4 Replies
Can you be a bit clearer on what you are trying to achieve?
- I don't see a Table 3 with your expected result. Create your expected result in Excel manually and paste an image of what you want it to look like, and fully explain the logic.
- Can you re-paste your table data using the info in the "How to provide sample data..." link below? Otherwise there is a lot of cleanup work to do for someone to help.
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 ForumDear 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] ) & ""
