Forum Discussion

amotto11's avatar
amotto11
Helper II
9 years ago

Ranking

I am new to Power BI and Dax all together and am trying to learn.   I have a table with 3 columns: ID, Company, Amount. I am trying to rank the amount from the company by ID. I believe i need a mea...
dkay84_PowerBI's avatar
dkay84_PowerBI
Microsoft Employee
9 years ago

I took a very different approach to solving this.  I used the query editor to add the Rank by creating a custom function that would sort the value field in ascending order and then add an index (starting with 1).  Then, I grouped the original data table by ID, with the aggregation set to "All Rows".  Then, invoke this custom function as a new column and it will give you a rank column after expanding the table.

 

Here is the function:

let
    Source = (column) as table => let
        Source = column,
        #"Sorted Rows" = Table.Sort(Source,{{"Value", Order.Ascending}}),
        #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Rank", 1, 1)
    in
        #"Added Index"
in
    Source

Then, using DAX, I created the following 3 measures:

Rank1 = IF(CALCULATE(COUNT(Table1[Index]),Table1[Index]="1")=0,0,CALCULATE(COUNT(Table1[Index]),Table1[Index]="1"))
Rank2 = IF(CALCULATE(COUNT(Table1[Index]),Table1[Index]="2")=0,0,CALCULATE(COUNT(Table1[Index]),Table1[Index]="2"))
Total = [Rank1]+[Rank2]

After putting these into a table with the Company field set to "Show Items With no Data" and "Do Not Summarize", you get the following:

 

 

amotto11's avatar
amotto11
Helper II
9 years ago

Wow, I did not even know the query editor existed, but as I said I am very new to Power BI. It is great to see so many different ways to solve a problem, it really helps someone trying to learn, Thanks!

 

Based on these two methods, which do you think is more efficient, which method should one use going forward?

  • dkay84_PowerBI's avatar
    dkay84_PowerBI
    Microsoft Employee
    9 years ago

    In general, any time you can bring in a column from the source data or from the query editor, it will have better compression.  However, with measures, it is better to use DAX.  In the case of the RankX measure, it is probably better to use DAX, but for my sanity I used the query editor ;)