RANKX issues using DAX

Question

I was working on the conversion of a sql request into a dax one :

    >;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<=(DATEADD(month, -1, GETDATE())) 
    >)                   
    >SELECT COUNT(idU_Email)
    >    FROM   cte
    >    WHERE  rang = 1 
    >    and valeur = 1

If i understood well, the equivalent of the rownumber function in sql in the RANKX Function in DAX. So basically, to achieve the conversion i created a new calculated colum with the following expression :

Ranking on partition

To resume it, it makes a ranking on every parition of idU_Email order by dRecueil and then by valeur.

Thus, the only thing left to do was to add a condition on the date (like in my SQL request), but i guess i don't really know how (or where?), and an error occurred when i tried, which i haven't achieved to solved yet :

Ranking on partition with filter on date

I hope someone would find a way to solve that issue (or even to propose me a better way to have the equivalent of my SQL request).

Thanks by advance ! Smiley Happy

[UPDATE]

I've achieved to partition as I wanted to do and to apply a date filter like that :

RANK_OnDate = IF(vUE[dRecueil] <= DATE(YEAR(TODAY()); MONTH(TODAY())-1; DAY(TODAY()))
            ;RANKX(FILTER(vUE; vUE[idU_Email] = EARLIER(vUE[idU_Email]) && 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
                )
            )
        )

But, actually, i would like it to filter dynamically... Thus i don't know if it would be better to use that directly in a measure (according that i want to count distinctly the lower/higher rank for each idU_email).

Additionally my filter just apply blank when the date isn't ok with the filter but the ranking stay the same...

I tried to do directly the distinct count in a measure but can't save the different issues encountered... Have you got some ideas ?

(and thanks for your answers) :)

What are you filtering on that you need to to be dynamic? How do you want the measure to act "when the date isn't ok with the filter"? — Alexis Olson

Alexis Olson Alexis Olson · Accepted Answer · 2018-03-22T17:27:49

I think you're pretty close, try this

Rank = IF(vUE[dRecueil] <= DATE(YEAR(TODAY()), MONTH(TODAY())-1, DAY(TODAY())),
           RANKX(FILTER(vUE, vUE[idU_Email] = EARLIER(vUE[idU_Email])),
               RANKX(ALL(vUE), vUE[dRecueil], ,ASC) +
               DIVIDE(
                   RANKX(ALL(vUE), vUE[valeur], , DESC, Skip),
                   COUNTROWS(ALL(vUE)) + 1)))

RANKX issues using DAX

1 Answers