Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
MadhumithaV_26
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

 

CountryGroup NameFlag
AAAGroup 1Yes
BBBGroup 2No
CCCGroup 3No
DDDGroup 1Yes
DDDGroup 2Both
EEEGroup 3No
FFFGroup 1Yes
FFFGroup 3Both
GGGGroup 3

 

No

 

 

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

Thanks in advance !!

1 ACCEPTED SOLUTION
Dangar332
Super User
Super User

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

 

 

Dangar332_0-1699363434482.png

 

View solution in original post

2 REPLIES 2
FreemanZ
Super User
Super User

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:

FreemanZ_0-1699364484577.png

 

Dangar332
Super User
Super User

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

 

 

Dangar332_0-1699363434482.png

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.