How to use RankX in POWER BI with 2 values with criteria

Question

I have below table in Power Bi and would like to add calculated column based on two columns, but ranking without tie in order of below combination. for example:

ORDER = IF [MAX GOAL) > 0% AND [TAG]='FALSE' THEN [DISTINCT COUNT]
IF [MAX GOAL) > 0% AND [TAG]='TRUE' THEN [DISTINCT COUNT]
IF [[TAG] ='FALSE' THEN [DISTINCT COUNT]
IF [TAG]='TRUE' THEN [DISTINCT COUNT]

Here is my table sample with desired result:

Order Company#  Company_Name    Goal_Name   Max_GOAL    TAG
    1   1001    COMPANY NAME 1  GOAL 1      25%         FALSE
    2   1001    COMPANY NAME 1  GOAL 2      81%         FALSE
    3   1001    COMPANY NAME 1  GOAL 3      40%         FALSE
    4   1001    COMPANY NAME 1  GOAL 4      26%         TRUE
    5   1001    COMPANY NAME 1  GOAL 5      0%          FALSE
    6   1001    COMPANY NAME 1  GOAL 6      0%          TRUE
    1   1002    COMPANY NAME 2  GOAL 1      26%         FALSE
    2   1002    COMPANY NAME 2  GOAL 2      66%         FALSE
    3   1002    COMPANY NAME 2  GOAL 3      40%         FALSE
    4   1002    COMPANY NAME 2  GOAL 4      68%         TRUE
    5   1002    COMPANY NAME 2  GOAL 5      0%          FALSE
    6   1002    COMPANY NAME 2  GOAL 6      0%          FALSE

Here is also the sample code I tried and worked in case someone else need it:

    RANKX(FILTER (
        GroupGoalMapping,
        GroupGoalMapping[GroupNumber] = EARLIER ( GroupGoalMapping[GroupNumber] ) 
    ),
    RANKX ( ALL ( GroupGoalMapping ), GroupGoalMapping[AnnualMaximum] * IF(GroupGoalMapping[AlternateGoal]="FALSE",2),, DESC )
        + DIVIDE (
            RANKX ( ALL ( GroupGoalMapping ), GroupGoalMapping[AnnualMaximum] * IF(GroupGoalMapping[AlternateGoal]="TRUE",1)  ,, DESC ),
            COUNTROWS ( ALL ( GroupGoalMapping ) ) + 1
        )
        + DIVIDE (
            DIVIDE (
                RANKX ( ALL ( GroupGoalMapping ), GroupGoalMapping[GoalName_Order] ,, ASC,Dense ),
                ( COUNTROWS ( ALL ( GroupGoalMapping ) ) + 1 )
            ),
            COUNTROWS ( ALL ( GroupGoalMapping ) ) + 1
        ),
    ,
    ASC,
    DENSE
)

Can you provide a text output and the desired output as a table? Also, can you define [DISTINCT COUNT]? — Angelo Canepa
@AngeloCanepa Thanks! I added the text in snippet and I need ranking or counting without tie. — Nil R

Nil R Nil R · Accepted Answer · 2020-09-30T13:13:22

I figured it out:

RANKX(FILTER (
            GroupGoalMapping,
            GroupGoalMapping[GroupNumber] = EARLIER ( GroupGoalMapping[GroupNumber] ) 
        ),
        RANKX ( ALL ( GroupGoalMapping ), GroupGoalMapping[AnnualMaximum] * IF(GroupGoalMapping[AlternateGoal]="FALSE",2),, DESC )
            + DIVIDE (
                RANKX ( ALL ( GroupGoalMapping ), GroupGoalMapping[AnnualMaximum] * IF(GroupGoalMapping[AlternateGoal]="TRUE",1)  ,, DESC ),
                COUNTROWS ( ALL ( GroupGoalMapping ) ) + 1
            )
            + DIVIDE (
                DIVIDE (
                    RANKX ( ALL ( GroupGoalMapping ), GroupGoalMapping[GoalName_Order] ,, ASC,Dense ),
                    ( COUNTROWS ( ALL ( GroupGoalMapping ) ) + 1 )
                ),
                COUNTROWS ( ALL ( GroupGoalMapping ) ) + 1
            ),
        ,
        ASC,
        DENSE
    )

How to use RankX in POWER BI with 2 values with criteria

1 Answers