cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PBI-Bro
Frequent Visitor

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

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!

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-lili6-msft
Community Support
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!