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
edhans's avatar
edhans
Community Champion
6 years ago

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'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...