cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

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

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

1 ACCEPTED SOLUTION
Super User

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

2 REPLIES 2
Super User

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:

Super User

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors