Using RANKX in PowerBI DAX

1
votes

I am a PowerBI newbie and I have been playing with DAX functions, more specifically, the RANKX function. Here is my data set:

+----------+-------------------------------------+-----------------+----------+
| Category |            Sub Category             |      Date       | My Value |
+----------+-------------------------------------+-----------------+----------+
| A        |              A1                     |      2018-01-01 |        2 |
| A        |              A2                     |      2018-01-02 |        4 |
| A        |              A3                     |      2018-01-03 |        6 |
| A        |              A4                     |      2018-01-04 |        6 |
| B        |              B1                     |      2018-01-05 |       21 |
| B        |              B2                     |      2018-01-06 |       22 |
| B        |              B2                     |      2018-01-07 |       23 |
| C        |              C1                     |      2018-01-08 |       35 |
| C        |              C2                     |      2018-01-09 |       35 |
| C        |              C3                     |      2018-01-10 |       35 |
+----------+-------------------------------------+-----------------+----------+

And below is my code:

Rank all rows as Column = 
    RANKX(
        'Table',
        'Table'[My Value]
        )

Unfortunately, I am getting the following error:

A single value for column 'My Value' in table 'Table' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Any help would be greatly appreciated.

Thanks

2
sqlpowerbidaxrankpowerquery
are you calculating a column or a measure? – RADO
@RADO having a column that ranks all the "My Value" is what would be ideal – Ali Parahoo

2 Answers

1
votes

There is nothing wrong with your formula, you just put it in a wrong place.

There are 2 ways you can write DAX formulas in PowerBI:

  • as a calculated column
  • as a measure

enter image description here

The difference is critical, you need to learn it if you want to use PowerBI.

The formula you wrote is for calculated columns. If you create it as a measure, you will get an error. To fix the problem, go to tab "Model", click "New Column", paste your code and it should work.

If you need RANKX as a measure, Chrisoffer has given you a good answer.

1
votes

Create a mesure to sum "My value" column:

Sum value = SUM(Table[My value])

Then use this measure to get your rank:

Rank all rows as Column = 
    RANKX(ALL(Table);[Sum value])

This will give you the rank of each sub category.