Ranking

[amotto11]

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 measure of Total_Amount and Rank, but cannot seem to figure out the RankX requirements and how it works.

I have created a measure of Total_Amount = sum(Table1[Amount])

Here is a sample of the raw data:

 ID      Company               Amount

1         CompanyA                 44

1         CompanyB                 45

1         CompanyC                 100

2         CompanyA                 60

2         CompanyB                 20

2         CompanyC                 80

2         CompanyD                 200

3         CompanyA                 1123

3         CompanyB                 1000

3         CompanyD                 2000

3         CompanyA                 2200

I would like the rank function to be based on the company, so rank the companies by ID by Amount. So the output would look something like this.

 ID      Company               Amount            Rank

1         CompanyA                 44                    1

1         CompanyB                 45                    2

1         CompanyC                 100                  3

2         CompanyA                 60                    2

2         CompanyB                 20                    1

2         CompanyC                 80                    3

2         CompanyD                 200                  4

3         CompanyA                 1123                2

3         CompanyB                 1000                1

3         CompanyC                 2000                3

3         CompanyD                 2200                4

My overall goal would be to transform this data to something like this:

Company            Total        Count Rank 1         Count Rank 2

Company A             3                      1                             2

Company B             3                      2                             1

Company C             2                      0                             0

Company D             2                      0                             0

Then if i filter out companies, the rank function would also filter them out and not include them etc. I hope all of this makes sense, but please let me know if you have any questions. Any help is greatly appretiated, as i am just getting started with Power BI.

[amotto11]

I was able to get to my second table shown, the ranking table. I used two measures.

Total_Amount= sum(Table1[Amount])

Rank = IF(ISBLANK([Total_Amount]),BLANK(),RANKX(FILTER(ALLSELECTED(Table1[Company]),NOT(ISBLANK([Total_Amount]))),[Total_Amount],,1,Dense))

Now I am trying to reach my final table, the one counting the rankings. Any help on this or reviewing my previous formula would be appretiated. Thanks

[Sean]

@amotto11

Ranks ASC =
IF (
    HASONEVALUE ( 'Table1'[Company] ),
    RANKX (
        ALL ( 'Table1'[Company] ),
        CALCULATE (
            SUM ( Table1[Amount] ),
            ALLEXCEPT ( 'Table1', 'Table1'[ID], 'Table1'[Company] )
        ),
        ,
        ASC
    )
)

@amotto11Here's what I get with your formula

[amotto11]

Well i am making a sample set that i thought was similar to my data, but apparently not. When i recreated the sample set i get what you are experiencing. In my original data, my formula is working, but yours does not have a rank of 1, it is starting with rank 2.

[Sean]

As long as you got it working -that's what matters!

[amotto11]

So i got my count formula for all the ID's to be this

Total Count = COUNTX(FILTER(ALLSELECTED(Table1[ID]),NOT(ISBLANK([Total_Amount]))),[Total_Amount])

Do you know how i can count the number ranked 1, ranked 2, etc?

I have to imagine it is something similar to my total count formula, I just can't do the if ranked 1 etc portion.

It will look something like my last table in my original post. I am posting my total count formula since my data that i provided is a little different than my source data, so hopefully that can help you.

[amotto11]

I think i got the Rank 1, and Rank 2 as seperate functions...

Rank 1 Count = IF(ISBLANK(COUNTX(FILTER(ALLSELECTED(Table1[ID]),[Rank]=1),[Total_Amount])),0,COUNTX(FILTER(ALLSELECTED(Table1[ID]),[Rank]=1),[Total_Amount]))

Is there a dynaminc function i can use, where i don't have to have 5 formulas for the top 5? I can just use one and filter on the top x that i want, or would i have to create another table with the numbers 1, 2, 3, 4... and go about it that way?

[dkay84_PowerBI]

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]

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]

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 😉

[dkay84_PowerBI]

I just noticed the very last ask you had for the rank to be dynamic based on the filter context, so this solution will not work for that.

After looking at the DAX rank measure provided by others in this thread, it does not appear to dynamically update either.

[Sean]

---

@dkay84_PowerBI wrote:

After looking at the DAX rank measure provided by others in this thread, it does not appear to dynamically update either.

---

Compression What does compression have to do with my Measure not updating dynamically?

[dkay84_PowerBI]

Compression has nothing to do with your measure updating dynamically. That was a response to the question about using query editor vs DAX.

As for dynamically updating, my interpretation was that the rank would change depending on the companies included in the filter/slicer. So for example, if only 2 companies are selected, the ranks would always be 1 & 2 since there are only 2 companies.

[amotto11]

Thank you all for your help. I have one last question.

My dataset is pretty large, over 5 million rows. When i use Import, i am able to use these DAX commands and i have it working, but it is extremely slow. just filtering by a few companies may take a few minutes to reload. Is DirectQuery any quicker? I have read that you cannot use DAX in DirectQuery mode, since that is the case, how would i go about doing what i have done in import in directquery mode?

[dkay84_PowerBI]

You can use DAX with Direct Query but there are restrictions on functions you can use.

In general, any slicing/filtering on large data sets will take time and resources. If your data source supports Direct Query, it will generally be faster as the filtering will get pushed to the underlying DB engine rather than run inside the Power BI data model.

[amotto11]

I tried to use my existing RankX and CountX functions and they would not work in DirectQuery, are these supported and i was just doing something wrong, or are they not supported?

[dkay84_PowerBI]

It looks like RankX is not supported:

https://msdn.microsoft.com/en-us/library/mt723603.aspx

However, if you go to the File > Options > Direct Query menu, you can check the box for unrestricted measures.  This allows all DAX but won't necessarily work if the backend DB doesn't support it since with DQ the queries get sent to the DB for execution.

[amotto11]

When i do my countx formula for the total it seems to work, but when i do my visual i get

Couldn't load the data for this visual

The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' row.

Is this just saying that my dataset is too large for directquery?

[dkay84_PowerBI]

Yes.  You need to aggregate the data so that the rows returned are below that limit.

[amotto11]

This may be a stupid question, but no question is stupid right...

How do I aggregate the data?

would i break the table into x number of tables, where x is the number of companies in the database? so each company has its own table and therefore its own information, then link them back up inside Power BI?

[dkay84_PowerBI]

Direct Query is designed more to return aggregations that the source DB performs, rather than return the raw data.  While filtering always consumes a lot of resources, with an import data model (rather than DQ) it is possible that your structure/model is partly to blame for the performance problems.  You should be using a star schema with lookup tables and fact tables.  I would recommend going back to an import data model and seeing if you can improve the model structure to get a better response time when slicing the data.