cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Antoine1M
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
v-jiascu-msft
Microsoft
Microsoft

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.

Hi @v-jiascu-msft ! 🙂
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 ! 🙂

 

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors