ROW_NUMBER() OVER (PARTITION BY A,B,C,D,E ORDER BY DBMS_RANDOME.RANDOM) IN DAX ??

[Anonymous]

Hello, Community....first-timer here--

So, I have a fairly large aggregation table (AGG) that I am merging (full outer join) with a smaller table (ISS) using seven join conditions, i.e. full outer join table b on a.column = b.column x 7. Upon merging (about 2 hrs run time), I noticed that my figures were inflated, so I implemented a row_number function to "de-dupe" the dataset and normalize the aggregations from the smaller table. It worked perfectly; however, it tripled the CI build duration. 

Below is a screen grab of a few of the fields in the final merged table--RO_ISS = 1 are being counted while RO_ISS = 2 are being excluded from PBI visuals (example measure replicated for each column in ISS: 

tot_issues_upd =

calculate(

    SUM(TBL_CUSTCA_PRANET_MERGED[Total Issues]), filter(TBL_CUSTCA_PRANET_MERGED, TBL_CUSTCA_PRANET_MERGED[RO_ISS] = 1)

)

This brings me to my question....I decided to attempt the row_number function as a calculated column in PBI as an alternative to the SQL window function with the hopes of reducing the CREATE TABLE duration. Can someone help me with this? My intitial approach was to create a custom column concatenating the seven columns present in my SQL PARTITION BY clause into one, long underscore-separated string, then using a combination of RANKX and FILTER. It seems to work in many cases, but many are being assigned the rank of 2 for no apparent reason. 

Thanks!

[amitchandak]

@Anonymous you have follow Ranking by Sub Category in the given blog and you will few such

example

[A] = MAx([A]) && [B] = Max([B])  etc

https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures