Forum Discussion

smpa01's avatar
smpa01
Community Champion
3 years ago
Solved

Ranking issue

AlexisOlson CNENFRNL  I  am trying to create Ranking by using SUBSTITUTEWITHINDEX . It is working as desired in a table expression.   baseTable | name | row | cat | rowCopy | |--------|-...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi smpa01 ,

    I updated your sample pbix file(see the attachment), please check if that is what you want. You can create a measure as below to get the rank...

    Measure = 
VAR tab =
    FILTER (
        ALLSELECTED ( 'wc' ),
        'wc'[Win] <> 0
            && 'wc'[Region] = SELECTEDVALUE ( 'wc'[Region] )
    )
VAR result =
    SUBSTITUTEWITHINDEX (
        'wc',
        "rank", SUMMARIZE ( tab, 'wc'[Win] ),
        'wc'[Win], DESC
    )
VAR _rank =
    MAXX ( result, [rank] )
RETURN
    IF ( ISBLANK ( _rank ), BLANK (), _rank + 1 )

    In addition,  you can achieve the same requirement using RANKX function. Please create a measure as below to get it, it is easier...

    Rank =
RANKX (
    FILTER ( ALLSELECTED ( 'wc' ), 'wc'[Region] = SELECTEDVALUE ( 'wc'[Region] ) ),
    CALCULATE ( SUM ( 'wc'[Win] ) ),
    ,
    DESC,
    DENSE
)

    Best Regards

  • AlexisOlson's avatar
    AlexisOlson
    3 years ago

    This feels a bit for efficient since it's iterating over regions instead of every row:

    Table 3 =
SELECTCOLUMNS (
    GENERATE (
        SUMMARIZE ( wc, wc[Region] ),
        VAR _Region = wc[Region]
        VAR _Partition_ =
            SELECTCOLUMNS ( FILTER ( wc, wc[Region] = _Region ), wc[Country], wc[Win] )
        VAR _AddCol_ =
            ADDCOLUMNS ( _Partition_, "@win", wc[Win] )
        VAR _SubIndex_ =
            SUBSTITUTEWITHINDEX (
                _AddCol_,
                "Rank", SUMMARIZE ( _AddCol_, [@win] ),
                [@win], DESC
            )
        RETURN
            _SubIndex_
    ),
    "Region", wc[Region],
    "Country", wc[Country],
    "Win", wc[Win],
    "Rank", [Rank] + 1
)
smpa01's avatar
smpa01
Community Champion
3 years ago

Anonymous  many thanks !!! well done. 

Just wondering, if it is possible to achieve a ranking by a partition with SUBSTITUTEWITHINDEX 

E.g.

 

wc table

| Region        | Country   | Win |
|---------------|-----------|-----|
| South America | Brazil    | 5   |
| South America | Argentina | 3   |
| South America | Chile     | 0   |
| South America | Uruguay   | 2   |
| Europe        | Germany   | 4   |
| Europe        | Italy     | 4   |
| Europe        | Austria   | 0   |

end goal - Ranking of Win by Region Partition

| Region        | Country   | Win | Rank |
|---------------|-----------|-----|------|
| South America | Brazil    | 5   | 1    |
| South America | Argentina | 3   | 2    |
| South America | Uruguay   | 2   | 3    |
| Europe        | Germany   | 4   | 1    |
| Europe        | Italy     | 4   | 1    |

 

pbix is attached

 

AlexisOlson CNENFRNL 

 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi smpa01 ,

I updated your sample pbix file(see the attachment), please check if that is what you want. You can create a measure as below to get the rank...

Measure = 
VAR tab =
    FILTER (
        ALLSELECTED ( 'wc' ),
        'wc'[Win] <> 0
            && 'wc'[Region] = SELECTEDVALUE ( 'wc'[Region] )
    )
VAR result =
    SUBSTITUTEWITHINDEX (
        'wc',
        "rank", SUMMARIZE ( tab, 'wc'[Win] ),
        'wc'[Win], DESC
    )
VAR _rank =
    MAXX ( result, [rank] )
RETURN
    IF ( ISBLANK ( _rank ), BLANK (), _rank + 1 )

In addition,  you can achieve the same requirement using RANKX function. Please create a measure as below to get it, it is easier...

Rank =
RANKX (
    FILTER ( ALLSELECTED ( 'wc' ), 'wc'[Region] = SELECTEDVALUE ( 'wc'[Region] ) ),
    CALCULATE ( SUM ( 'wc'[Win] ) ),
    ,
    DESC,
    DENSE
)

Best Regards

  • smpa01's avatar
    smpa01
    Community Champion
    3 years ago

    Anonymous  I know how to achieve the same with RANKX. I just wanted to see how can I do the same with a different DAX function

  • smpa01's avatar
    smpa01
    Community Champion
    3 years ago

    Anonymous  follow up question.

    In a pure table expression, how can I return a rank by partition.

    For example, I can achieve this with RANKX as following

    Table 4 =
ADDCOLUMNS (
    FILTER ( wc, wc[Win] <> 0 ),
    "rank", RANKX ( FILTER ( wc, wc[Region] = EARLIER ( wc[Region] ) ), wc[Win],, DESC )
)

    How can I do the same with SUBSTITUTEWITHINDEX in a pure table expression

    I tried this

Table 3 =
VAR partitionFirst =
    CALCULATE ( MAX ( wc[Region] ) )
VAR base =
    FILTER ( ALL ( wc ), wc[Win] <> 0 && wc[Region] = partitionFirst )
VAR rankedTbl =
    SUBSTITUTEWITHINDEX (
        ADDCOLUMNS ( wc, "@win", wc[Win] ),
        "rank", SUMMARIZE ( base, [Win] ),
        wc[Win], DESC
    )
RETURN
    rankedTbl

    but it did not work

     

    I know I can do this. But I am not looking for this.

     

     

    • AlexisOlson's avatar
      AlexisOlson
      Super User
      3 years ago

      smpa01 Here's how I'd modify your attempt:

      Table 3 = 
VAR _NonZero_ = FILTER ( wc, wc[Win] <> 0 )
VAR _Ranked_ =
    ADDCOLUMNS (
        _NonZero_,
        "Rank",
            VAR _Region = wc[Region]
            VAR _Country = wc[Country]
            VAR _Partition_ = FILTER ( _NonZero_, wc[Region] = _Region )
            VAR _AddCol_ = ADDCOLUMNS ( _Partition_, "@win", wc[Win] )
            VAR _SubIndex_ = SUBSTITUTEWITHINDEX ( _AddCol_, "@Rank", SUMMARIZE ( _AddCol_, [@win] ), [@win], DESC )
            VAR _Row_ = FILTER ( _SubIndex_, wc[Country] = _Country )
            VAR _Rank = MAXX ( _Row_, [@Rank] ) + 1
            RETURN
                _Rank
    )
RETURN
    _Ranked_
      • AlexisOlson's avatar
        AlexisOlson
        Super User
        3 years ago

        This feels a bit for efficient since it's iterating over regions instead of every row:

        Table 3 =
SELECTCOLUMNS (
    GENERATE (
        SUMMARIZE ( wc, wc[Region] ),
        VAR _Region = wc[Region]
        VAR _Partition_ =
            SELECTCOLUMNS ( FILTER ( wc, wc[Region] = _Region ), wc[Country], wc[Win] )
        VAR _AddCol_ =
            ADDCOLUMNS ( _Partition_, "@win", wc[Win] )
        VAR _SubIndex_ =
            SUBSTITUTEWITHINDEX (
                _AddCol_,
                "Rank", SUMMARIZE ( _AddCol_, [@win] ),
                [@win], DESC
            )
        RETURN
            _SubIndex_
    ),
    "Region", wc[Region],
    "Country", wc[Country],
    "Win", wc[Win],
    "Rank", [Rank] + 1
)