Forum Discussion
RANKX ADDCOLUMNS Variable table syntax issue
Hi All, I need help wiht a rankx within a variable table. I need to use variables instead of calculated columns as the measure I am using is dynamic and I don't want to use multiple calculated columns and switch functions.
This is the first part of the solution, I will then add percentile rank and band results.
TestTable:
| Dimension | Value |
| A | 1 |
| B | 2 |
| C | 3 |
| D | 4 |
Dax code attempt:
Rank =
Var Tbl = SUMMARIZE('TestTable',
'TestTable'[Dimension],
"Measure",SUM(TestTable[Value]))
Var AddRank = ADDCOLUMNS(Tbl,"Rank",RANKX(Tbl,[Measure]))
RETURN
SUMX(AddRank,[Rank])
The dax is currently returning a 1 for each dimension. Is there a way to use Rankx from a variable that is not a calculated column? I'm thought I might have seen a post perhaps related to this before following a really long search I cannot find a solution that works.
Hi Anonymous
one solution I found is the following formula (see figure 1), but it is equal with the shorter formula in figure 2.
Figure 1
Figure 2
Some notes about your formula:
- You have used a varibale to create a virtual table, here: Var Tbl. In RANKX() you need the ALL() function that expects only a table reference, not a table expression like Tbl.
- The measure Sum of TestTable[Value] should be calculated outside the formula.
With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)
2 Replies
Hi Anonymous
one solution I found is the following formula (see figure 1), but it is equal with the shorter formula in figure 2.
Figure 1
Figure 2
Some notes about your formula:
- You have used a varibale to create a virtual table, here: Var Tbl. In RANKX() you need the ALL() function that expects only a table reference, not a table expression like Tbl.
- The measure Sum of TestTable[Value] should be calculated outside the formula.
With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)- Thakns so much FrankAT, exactly what I need greatly appreciated.