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 |
|--------|-----|------|---------|
| john   | 1   | cat2 | 1       |
| jane   | 2   | cat3 | 2       |
| joanne | 3   | cat1 | 3       |
| jenny  | 4   | cat4 | 4       |
| jones  | 5   | cat1 | 5       |
| jeff   | 6   | cat7 | 6       |

I want to generate a Ranking of a filtered table of the above (cat=cat1).

I want to achieve this

| name   | cat  | rowCopy | rank |
|--------|------|---------|------|
| joanne | cat1 | 3       | 0    |
| jones  | cat1 | 5       | 1    |

I can achieve the above with following
Table 2 =
VAR filt =
    FILTER ( 'Table', 'Table'[cat] = "cat1" )
VAR new2 =
    SUBSTITUTEWITHINDEX (
        'Table',
        "rank", SUMMARIZE ( filt, 'Table'[row] ),
        'Table'[row], 1
    )
RETURN
    new2

 

But it is not working in a Measure. Is it possible to make it work in a Measure?

The pbix is attached.

Thank you in advance.

 

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
)

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi smpa01 ,

    Please update the formula of measure as below and you can get the desired result... You can find the details in the attachment.

    Measure = 
VAR filt =
    FILTER ( ALLSELECTED ( 'Table' ), 'Table'[cat] = "cat1" )
VAR new2 =
    SUBSTITUTEWITHINDEX (
        'Table',
        "rank", SUMMARIZE ( filt, 'Table'[row] ),
        'Table'[row], 1
    )
RETURN
    MAXX ( new2, [rank] )

    Best Regards

    • 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

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    3 years ago

    It seems I've arrived a bit late. But frankly speaking, I've got no idea about SUBSTITUTEWITHINDEX(), I'll take a look at it.

     

    Happy new year and enjoy DAX!