topic Row by Over Partion in Dax in DAX Commands and Tips

Row by Over Partion in Dax

Anonymous — Mon, 27 Dec 2021 21:44:46 GMT

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_calls

The 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!

Re: Row by Over Partion in Dax

wdx223_Daniel — Tue, 28 Dec 2021 00:57:41 GMT

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

Re: Row by Over Partion in Dax

smpa01 — Tue, 28 Dec 2021 03:37:54 GMT

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 )

Re: Row by Over Partion in Dax

Anonymous — Tue, 28 Dec 2021 04:26:50 GMT

Thank you!!
It worked I appreciate your help :)))

Re: Row by Over Partion in Dax

Anonymous — Tue, 28 Dec 2021 04:39:06 GMT

Thank you too!

Your method works for me as well!!

I appreciate the help:))