Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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
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
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |