How to create Ranking Column with Multiple Conditions

[NilR]

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 , however I do not know how to encounter other criterias:

RANKX (
    FILTER (
        GroupGoalMapping,
        GroupGoalMapping[Company#] = EARLIER ( GroupGoalMapping[Company#] )
    ),
    GroupGoalMapping[Goal_MAX],
    ,
    DESC,
    DENSE
)

[v-lili6-msft]

hi  @NilR 

Do you mean you want this?

Column = RANKX (
    FILTER (
        GroupGoalMapping,
        GroupGoalMapping[Company#] = EARLIER ( GroupGoalMapping[Company#] )&&GroupGoalMapping[TAG]=EARLIER(GroupGoalMapping[TAG])
    ),
    GroupGoalMapping[Max GOAL],
    ,
    DESC,
    DENSE
)

I could not see your desired result in this table sample.

and here is blog about Ranking Column with Multiple Conditions, you could refer to:

https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns

https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

Regards,

Lin

[NilR]

Thanks for your reply! The order column is actually my desire out come! does it make sense?

[DataInsights · Accepted solution]

@NilR, try these calculated columns. The column [Order] is based on the column [Group Order]. The column [Group Order] uses the logic you provided, assigning the appropriate group. The [Group Order] column also concatenates [Goal Name] in order to create a unique column for ranking.

Group Order = 
VAR vGoalName = GroupGoalMapping[Goal Name]
VAR vMaxGoal = GroupGoalMapping[Max GOAL]
VAR vTag = GroupGoalMapping[TAG]
VAR vGroup =
    SWITCH (
        TRUE (),
        vMaxGoal > 0
            && vTag = "FALSE", 1,
        vMaxGoal > 0
            && vTag = "TRUE", 2,
        vTag = "FALSE", 3,
        vTag = "TRUE", 4
    )
VAR vResult = vGroup & "-" & vGoalName
RETURN
    vResult

Order = 
VAR vCompany = GroupGoalMapping[Company#]
VAR vCompanyTable =
    FILTER ( GroupGoalMapping, GroupGoalMapping[Company#] = vCompany )
VAR vResult =
    RANKX ( vCompanyTable, GroupGoalMapping[Group Order],, ASC, DENSE )
RETURN
    vResult

[NilR]

worked Great- Thank you!