Forum Discussion

Antoine1M's avatar
Antoine1M
Frequent Visitor
8 years ago

SQL Request with Rownumber in PBI

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

Need Help

2 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    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's avatar
      Antoine1M
      Frequent Visitor
      8 years ago

      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