Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |