Forum Discussion

CJ_96601's avatar
CJ_96601
Helper V
6 years ago

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 ...
Need Help
CJ_96601's avatar
CJ_96601
Helper V
6 years ago

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      
  EmpNoNameGradeYEAR HIRE  
  1  2020  
  2  2016  
  3     
  4     
        
        
        
        
        
 Table 2      
  EmpNoNameGradePerformanceYear 
  1  E2019 
  1  E2018 
  1  VG2017 
  1  G2016 
  2  E2018 
  2  E2017 
  2  G2016 
  3  VG2019 
  3  E2018 
  4  POOR2020 
        
        
        
        
RESULT WHEN FILTERED BY YEAR    
        
FILTER YEAR 2020     
        
        
 RESULT USING MEASURE      
  EmpNoNameGradeYEAR HIREPerformance 
  1   E 
  2   - 
  3   VG 
  4   - 
        
        
        
        
v-chuncz-msft's avatar
v-chuncz-msft
Community Support
6 years ago

CJ_96601 

You may refer to the measure below.

 

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

 

  • CJ_96601's avatar
    CJ_96601
    Helper V
    6 years ago

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