Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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!
Solved! Go to Solution.
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
)
Proud to be a Super User!
New Animated Dashboard: Sales Calendar
@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
)
Proud to be a Super User!
New Animated Dashboard: Sales Calendar
Thank you too!
Your method works for me as well!!
I appreciate the help:))
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))
Thank you!!
It worked I appreciate your help :)))
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!