How to get the flag count for the countries which lies in both the group based on the custom logic?

[MadhumithaV_26]

Hi All,

Consider we have a table with a list of countries and group names assigned for each county. We need to create a Flag column that will have the values Yes, No & Both.

1. When the country has the Group Name as Group 1, the flag will be Yes

2. When the country has both Group 1 & Group 2 or Group 1 & Group 3, 
       a. the flag will be Both for the row that contains Group 2

       b. the flag will be Yes for the row that Group 1

3. In other case, the flag will be No

Country	Group Name	Flag
AAA	Group 1	Yes
BBB	Group 2	No
CCC	Group 3	No
DDD	Group 1	Yes
DDD	Group 2	Both
EEE	Group 3	No
FFF	Group 1	Yes
FFF	Group 3	Both
GGG	Group 3	No

Can you please help in achieving the flag logic through DAX?

Thanks in advance !!

[FreemanZ]

hi @MadhumithaV_26 ,

try to add a column like:

Column = 
SWITCH(
    TRUE(),
    [Group Name] = "Group 1", "Yes",
    VAR _list =CALCULATETABLE(VALUES(data[Group Name]), ALLEXCEPT(data, data[Country]))
    VAR _condition = "Group 1" IN _list && [Group Name]<>"Group 1"
    RETURN _condition,
    "Both", "No"
)

it worked like:

[Dangar332 · Accepted solution]

HI, @MadhumithaV_26 

try below measure

Measure 4 = 
var a = CALCULATE(
           DISTINCTCOUNT('Table'[Group Name]),
               REMOVEFILTERS('Table'[Group Name])
        )
var b = SWITCH(TRUE(),
           MAX('Table'[Group Name])="group 1","yes",
           a=2 && OR(MAX('Table'[Group Name])="group 2",
              MAX('Table'[Group Name])="group 3"),"both",
        "no"
         )
return b