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 = 0
If 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 !
Can you share the pbix file directly?
Where is the column "idU_Email"?
The first row seems to be this one below. Isn't it?
idU ||id_Type ||valeur ||dRecueil 1 ||1 ||0 ||03/04/2018