DAX Help

[CJ_96601]

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

[edhans]

Can you be a bit clearer on what you are trying to achieve?

1. 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.
2. 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 Forum

[CJ_96601]

Dear 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				-

[v-chuncz-msft]

@CJ_96601 

You may refer to the measure below.

Measure =
MAXX (
    FILTER (
        Table2,
        Table2[Year]
            = SELECTEDVALUE ( 'Calendar'[Year] ) - 1
    ),
    Table2[Performance]
) & ""

[CJ_96601]

Thanks...but it is not working...