Forum Discussion

zahirmohideen's avatar
zahirmohideen
Helper I
1 year ago
Solved

DAX equivalent for DENSE_RANK Window function with KEEP clause

Hello , 
 
Here is the sample use case. 
This table has all the job code for the employees and has one record per employee and hire_date.
What would be the DAX equivalent of the following sql "most_recent_job_code" ?
 
employee
--------
 
emp_id int 
hire_date date 
job_code varchar2(2)
sal number(9,2) 
 
SQL
===
SElect emp_id ,  
       MAX(job_code) 
   KEEP(DENSE_RANK FIRST ORDER BY hire_date DESC NULLS FIRST) AS most_recent_job_code
from 
employee
group by
emp_id ;
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    1 year ago

    Hi, 
    I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

    The below formula is for creating table, and I tried to use TREATAS and INDEX DAX functions.

     

     

    INDEX function (DAX) - DAX | Microsoft Learn

     

    TREATAS function - DAX | Microsoft Learn

     

    expected result table = 
SUMMARIZECOLUMNS (
    data[emp_id],
    data[job_code],
    data[sal],
    TREATAS (
        INDEX (
            1,
            SUMMARIZE ( data, data[emp_id], data[hire_date] ),
            ORDERBY ( data[hire_date], DESC ),
            ,
            PARTITIONBY ( data[emp_id] )
        ),
        data[emp_id],
        data[hire_date]
    )
)

     

3 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    1 year ago

    Hi zahirmohideen 

    Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    1 year ago

    Hi, 
    I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

    The below formula is for creating table, and I tried to use TREATAS and INDEX DAX functions.

     

     

    INDEX function (DAX) - DAX | Microsoft Learn

     

    TREATAS function - DAX | Microsoft Learn

     

    expected result table = 
SUMMARIZECOLUMNS (
    data[emp_id],
    data[job_code],
    data[sal],
    TREATAS (
        INDEX (
            1,
            SUMMARIZE ( data, data[emp_id], data[hire_date] ),
            ORDERBY ( data[hire_date], DESC ),
            ,
            PARTITIONBY ( data[emp_id] )
        ),
        data[emp_id],
        data[hire_date]
    )
)