Forum Discussion
Row by Over Partion in Dax
Hello everyone,
I hope you have a splendid week.
I encountered the following question, and I hope you might help me here.
I tried to "convert" a query in SQL into dax, and I didn't manage to do so.
Here is the SQL query:
select
ROW_NUMBER() OVER (PARTITION BY employee_id, customer_id ORDER BY Increament_copy_datetime desc) as row_num,
employee_id,
customer_id,
call_status,
Increament_copy_datetime
from tess_reports.fact_callsThe results I'm expecting to get are the one marked in yellow (The one with the highest rank, means the one with the rank of 1).
The idea here is to create a new table with the "yellow-marked" results as a variable (I plan to use this table with other filters and manipulations like so).
I'll appreciate your kind help!
Thanks!
NewTable=TOPN(1,SourceTable,RANKX(ALL(SourceTable[Increament_copy_datetime]),CALCULATE(MAX(SourceTable[Increament_copy_datetime]),ALLEXCEPT(SourceTable,SourceTable[employee_id],SourceTable[customer_id],SourceTable[Increament_copy_datetime])),,ASC))
Anonymous if you have a table like this
The TSQL's ROW_NUMBER() equivalent would be following which you can achieve with a measure from the exisitng table wothout creating any more table
Row_Number = RANKX ( FILTER ( ALLSELECTED ( fact_calls ), fact_calls[customer_id] = MAX ( fact_calls[customer_id] ) --equivalent of PARTITION BY employee_id && fact_calls[employee_id] = MAX ( fact_calls[employee_id] )--, customer_id ), [_Increament_copy_datetime], -- equivalent of ORDER BY Increament_copy_datetime desc , DESC )
4 Replies
- wdx223_Daniel
Community Champion
NewTable=TOPN(1,SourceTable,RANKX(ALL(SourceTable[Increament_copy_datetime]),CALCULATE(MAX(SourceTable[Increament_copy_datetime]),ALLEXCEPT(SourceTable,SourceTable[employee_id],SourceTable[customer_id],SourceTable[Increament_copy_datetime])),,ASC))
smpa01Anonymous if you have a table like this
The TSQL's ROW_NUMBER() equivalent would be following which you can achieve with a measure from the exisitng table wothout creating any more table
Row_Number = RANKX ( FILTER ( ALLSELECTED ( fact_calls ), fact_calls[customer_id] = MAX ( fact_calls[customer_id] ) --equivalent of PARTITION BY employee_id && fact_calls[employee_id] = MAX ( fact_calls[employee_id] )--, customer_id ), [_Increament_copy_datetime], -- equivalent of ORDER BY Increament_copy_datetime desc , DESC )