Forum Discussion

NiteshS's avatar
NiteshS
Icon for Helper I rankHelper I
4 years ago
Solved

Ranking value column in a table based on multiple other column

Dear All,

Can you please guide me in developing a DAX code to rank the "Point Estimate" column in the below table based on the other three columns? Thanks in advance. Fowmy 

StudyNameSubgroupOutcomePointEstimateRank
Study 1OverallResponse >=90%1.62
Study 1OverallConstipation3.45
Study 1OverallCEIs2.03
Study 1Stage IIIInfections2.14
Study 1Stage IVHematologic AE1.01
Study 2Stage IVDiarrhea3.56
Study 2OverallHypertension3.57
Study 2BRCA+Nausea3.68
Study 2BRCA-Anemia3.810
Study 2Stage IIIHematologic AE3.79
Study 2Stage IIIHematologic AE3.911
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi NiteshS ,

    According to your description, here's my solution.

    1. If based on the StudyName column, rank PointEstimate column.

    Add a new column, the DAX statement is as following:

     

    Column 1 =
RANKX (
    FILTER ( 'Table', 'Table'[StudyName] = EARLIER ( 'Table'[StudyName] ) ),
    'Table'[PointEstimate],
    ,
    DESC
)

     

    2. If based on the StudyName and Subgroup column, rank PointEstimate column.

    Add a new column, the DAX statement is as following:

     

    Column 2 =
RANKX (
    FILTER (
        'Table',
        'Table'[StudyName] = EARLIER ( 'Table'[StudyName] )
            && 'Table'[Subgroup] = EARLIER ( 'Table'[Subgroup] )
    ),
    'Table'[PointEstimate],
    ,
    DESC
)

     

    3. If based on the StudyName, Subgroup and Outcome column, rank PointEstimate column.

    Add a new column, the DAX statement is as following:

     

    Column 3 =
RANKX (
    FILTER (
        'Table',
        'Table'[StudyName] = EARLIER ( 'Table'[StudyName] )
            && 'Table'[Subgroup] = EARLIER ( 'Table'[Subgroup] )
            && 'Table'[Outcome] = EARLIER ( 'Table'[Outcome] )
    ),
    'Table'[PointEstimate],
    ,
    DESC
)

    4. Final output

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

4 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi NiteshS ,

    According to your description, here's my solution.

    1. If based on the StudyName column, rank PointEstimate column.

    Add a new column, the DAX statement is as following:

     

    Column 1 =
RANKX (
    FILTER ( 'Table', 'Table'[StudyName] = EARLIER ( 'Table'[StudyName] ) ),
    'Table'[PointEstimate],
    ,
    DESC
)

     

    2. If based on the StudyName and Subgroup column, rank PointEstimate column.

    Add a new column, the DAX statement is as following:

     

    Column 2 =
RANKX (
    FILTER (
        'Table',
        'Table'[StudyName] = EARLIER ( 'Table'[StudyName] )
            && 'Table'[Subgroup] = EARLIER ( 'Table'[Subgroup] )
    ),
    'Table'[PointEstimate],
    ,
    DESC
)

     

    3. If based on the StudyName, Subgroup and Outcome column, rank PointEstimate column.

    Add a new column, the DAX statement is as following:

     

    Column 3 =
RANKX (
    FILTER (
        'Table',
        'Table'[StudyName] = EARLIER ( 'Table'[StudyName] )
            && 'Table'[Subgroup] = EARLIER ( 'Table'[Subgroup] )
            && 'Table'[Outcome] = EARLIER ( 'Table'[Outcome] )
    ),
    'Table'[PointEstimate],
    ,
    DESC
)

    4. Final output

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • NiteshS's avatar
      NiteshS
      Icon for Helper I rankHelper I
      4 years ago

      Thank you.

       

      PREVIEW
      Yes, I am close to my answer. Is there a way I can give a dynamic row number that gets updated based on the selected filter/slicer?
       
      OR
       
      Is there a way to get rid of the ties? I need continuous ranking for the point estimate.