I was working on the conversion of a sql request into a dax one. The request is the following :
;WITH cte
AS
(
SELECT uc.idU_Email
,uc.id_Type
,uc.dRecueil
,valeur
,ROW_NUMBER() OVER(PARTITION BY idU_Email,id_Type ORDER BY dRecueil DESC, valeur ASC) AS rang
FROM vUE uc
WHERE uc.Id_Type=1 AND uc.dRecueil<=DATE(02/04/2018)
)
SELECT COUNT(idU_Email)
FROM cte
WHERE rang = 1
and valeur = 0If i understood well, the equivalent of the rownumber function in sql is the RANKX Function in DAX. So basically, to achieve the conversion i created a new calculated column.
To resume it, it makes a ranking on every partition of idU_Email (the primary key) and an other partition (inside the first one) for each id_Type, ordered it by dRecueil (a date) and then by valeur (a bit), and is filtered by a condition on the date (dRecueil).
Thus, I've achieved to partition as I wanted to do and to apply a date filter with the following expression :
> RANK_OnDate2 = IF(vUE[dRecueil] <= DATE(02/04/2018)
> ;RANKX(FILTER(vUE; vUE[idU_Email] = EARLIER(vUE[idU_Email]) && vUE[dRecueil] <= DATE(02/04/2018) && vUE[id_Type] = EARLIER(vUE[id_Type]))
> ;RANKX(ALL(vUE); vUE[dRecueil]; ;ASC)
> +
> DIVIDE(
> RANKX(ALL(vUE); vUE[valeur]; ; DESC; Skip)
> ;COUNTROWS(ALL(vUE)) + 1
> )
> )
> )
And finally, I count in a measure the distinct Idu_Email where RANK_OnDate2 = 1 and valeur =0, so that i get the same result than for my sql request.
The problem is that I would like it to filter dynamically with a date saved in a measure (used as a slicer)... Thus, i understand that column can't be filtered dynamically, and that i should do the equivalent of my sql request directly in a measure.
I tried to do directly the distinct count in a measure but I can't solve the different issues encountered...
Particularly, i haven't achieved to used the function earlier in a measure (or to find something that would do the same)....
The measure should globally look like something like that :
TESTOptout2 = IF(vUE[dRecueil] <= My_Measures[datefilter]
;CALCULATE( DISTINCTCOUNT(vUE[idU_Email])
;RANKX(FILTER(vUE; vUE[idU_Email] = EARLIER(vUE[idU_Email]) && vUE[dRecueil] <= My_Measures[datefilter] && vUE[id_Type] = EARLIER(vUE[id_Type]))
;RANKX(ALL(vUE); vUE[dRecueil]; ;ASC)
+ DIVIDE( RANKX(ALL(vUE); vUE[valeur]; ; DESC; Skip)
;COUNTROWS(ALL(vUE)) + 1
)
) = 1
;vUE[valeur]=0
)
)
Example : If we took a dataset like this one :
Table : vUE
idU_Email||id_Type ||valeur ||dRecueil
1 ||1 ||1 ||02/04/2018
1 ||1 ||0 ||03/04/2018
1 ||1 ||1 ||01/04/2018
1 ||1 ||1 ||03/04/2018
1 ||2 ||1 ||01/04/2018
2 ||1 ||1 ||01/04/2018
2 ||3 ||1 ||02/04/2018
3 ||2 ||1 ||01/04/2018
4 ||1 ||1 ||01/04/2018
4 ||1 ||1 ||03/04/2018
5 ||1 ||1 ||03/04/2018
The request will first order the data like this :
idU_Email||id_Type ||valeur ||dRecueil
1 ||1 ||0 ||03/04/2018
1 ||1 ||1 ||03/04/2018
1 ||1 ||1 ||02/04/2018
1 ||1 ||1 ||01/04/2018
1 ||2 ||1 ||01/04/2018
2 ||1 ||1 ||01/04/2018
2 ||3 ||1 ||02/04/2018
3 ||2 ||1 ||01/04/2018
4 ||1 ||1 ||03/04/2018
4 ||1 ||1 ||01/04/2018
5 ||1 ||1 ||03/04/2018
Then group by according to the date condition (on dRecueil) and the Id_Type, so if we keep the conditions of the sql request i would returned :
idU_Email||id_Type ||valeur ||dRecueil
1 ||1 ||1 ||02/04/2018
2 ||1 ||0 ||01/04/2018
4 ||1 ||0 ||01/04/2018
5 ||1 ||1 ||03/04/2018
And the final step of the request would be to count only where valeur= 0, so the request would have returned 2.
But I really have to use that only in a measure because i want my date condition to be modified dynamically with a slicer
Have you got some ideas ? Thanks by advance !
