topic Re: How to get the flag count for the countries which lies in both the group based on the custom logic? in DAX Commands and Tips

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

MadhumithaV_26 — Tue, 07 Nov 2023 12:22:01 GMT

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 !!

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

Dangar332 — Tue, 07 Nov 2023 13:54:06 GMT

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

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

FreemanZ — Tue, 07 Nov 2023 13:42:12 GMT

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: