Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Row_Number (By Partition)

Hi 

 

I saw identical question so using the same I  am also looking for ;

 

Select  DateKey ,Line_Number, 
        Suite_name, 
        Group_Name, 
        Revision, 
        Status, Met
 FROM (
    select  DateKey, Line_Number, 
            Suite_Name, 
            Group_Name, 
            Revision, 
            Status, Met,
            Row_Number() OVER (Partition by Line_Number, Suite_Name, Group_Name order by Revision Desc) r
    From TableA 
    ) twhere t.r = 1 and DateKey Between 20170101 AND 20170223

 

This query returns the highest Revision's row per Line_Number, Suite_Name, and Group_Name.

I want to do the same, but with DAX in SSAS Tabular so I can use the same in Power BI.

The result will be SUM of [Met] , but for the selected (slicers) .

The goal is to select all rows into the tabular data model, and then, using similar functionality, filter the records and keep only latest revision per applied slicers.

 

2 Replies

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

    Hi Anonymous,

     

    Can you share a dummy sample please? It's hard to write a DAX without data and its structure.

     

    Best Regards,

    Dale

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thanks for your reply !!

       

      Following is the sample,  where Surr, Mid, NID and TIN are the physical columns and Seq column values are getting using Row_Number function code which I have shared in first post. There are few more additional columns apart from shown in the below snap so one column named [Met] is there and we want to SUM  the values from column [Met] for Seq=1 like the way we are doing thru sql which can be found in first post.