cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Antoine1M Frequent Visitor
Frequent Visitor

Replacing EARLIER to use a measure

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 !

2 REPLIES 2
Community Support Team
Community Support Team

Re: Replacing EARLIER to use a measure

Hi @Antoine1M,

 

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

 

Best Regards,

Dale

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

Re: Replacing EARLIER to use a measure

Hi @v-jiascu-msft ! Smiley Happy
Sorry about that, i corrected (that was a mistake)
I'll try to share the pbix file today or ortherxise tomorrow if that's reallys necessary ! Smiley Happy