Translating a SQL Query with ROW_NUMBER() - well beyond my current skill-level

[PBI-Bro]

I managed to translate a bunch of queries from SQL to DAX, but I just hit a wall with this one. My DAX knowledge is still very limited.

Basically, I am trying to obtain one number (measure). The number of donors whose first donation was made via digital payment methods. My SQL looks like this:

SELECT COUNT(t.donor_id)
FROM (
   SELECT * FROM (
         SELECT
             ROW_NUMBER() OVER(PARTITION by donor_id  ORDER BY date) AS 'RowNumber',
             donor_id,
             date,
             value ,
             c.campaign_id
          FROM campaigns c
 ) AS s
 where s.RowNumber = 1 and s.campaign_id  like '%online%'
) AS t

My power BI model has the tables donations (fact table) and campaigns (dim table) among many others,  

I would appreciate your help on this one!

[Anonymous]

Hey, I have this SQL QUERY Can you help me to put in dax? 

Select Count ( Distinct ( ID)

FROM

(Select ID ,ROW_NUMBER () over (PARTITION BY [ID] ORDER BY ID_color, id_num, id ) R

FROM Table

) q

WHERE R= 1

[v-lili6-msft · Accepted solution]

hi  @PBI-Bro 

You could try this way as below:

1. create a rank measure as RowNumber

RowNumber = RANKX(FILTER(ALLSELECTED(campaigns),campaigns[donor_id]=MAX(campaigns[donor_id])),CALCULATE(MAX(campaigns[date])),,ASC)

2. create result measure

Measure 2 = COUNTAX(FILTER(campaigns,[RowNumber]=1&&SEARCH("online",[campaign_id],1,0)>0),[donor_id])

If not your case, please share your sample data and your expected output.

Regards,

Lin

[amitchandak]

@PBI-Bro , You have to do it by rank.

https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://databear.com/how-to-use-the-dax-rankx-function-in-power-bi/
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...
https://community.powerbi.com/t5/Desktop/RANKX-excluding-blanks/m-p/397656#M181491
https://databear.com/how-to-use-the-dax-rankx-function-in-power-bi/

You can explore index column

https://community.powerbi.com/t5/Desktop/Add-calculated-index-column-by-DAX/td-p/72448

https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi