SQL Request with Rownumber in PBI

[Antoine1M]

Hi guys ! 

I'm trying to calculate the equivalent of this request in PBI :

> ;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

 
But I have no idea at all on how to proceed...

My table looks like that : 


Help meeeee

[v-jiascu-msft]

Hi @Antoine1M,

Since the Id_Type is always 1, you can order the dRecuril desc and valeur acs in the Query Editor. Then add an index. The second sql statement could be like below.

measure = calculate(count('table'[idU_Email]), 'table'[index] = 1)

If you can share the pbix file, I can add the solution in it and send it back to you.

Best Regards,

Dale

[Antoine1M]

Hi @v-jiascu-msft, 

Actually, the Id_Type is not always 1, only in the sample of my screenshot.

I'd try to do what you said but got issues with the index... how to index on the partition of idU_Email and Id_Type ? The other issue is how to make this index with a filter on the date.

The pbix file is in this link (i've added what-if parameters so that i could change the condition on the date by the way):
https://www.transfernow.net/download/?utm_source=16f6k9l8diku