Creating a DAX Measure to count where a subgroup is greater than 1

Question

I have a dataset with multiple user logins and multiple districts as follows:

User	District	Region
UserA	HKG	China
UserA	PRG	Europe
UserA	CKG	China
UserB	LHR	Europe
UserB	AMS	Europe
UserB	TYO	Japan
UserA	CKG	China
UserC	SYD	Australia
UserC	MEL	Australia
UserD	SYD	Australia
UserD	MEL	Australia

I want to see the count of users that have logged into more than a single district within a region

Region	Created Measure
China	1
Europe	1
Japan	0
Australia	2

China has 1, because only UserA has logged into multiple China Districts Europe has 1, because only UserB has logged into multiple Europe Districts Japan has 0, because no user has logged into more than 1 Japan District Australia has 2, because both User C & D have logged onto multiple Australia Districts

I have gotten close with creating a table, but I can only seem to get it to count the distinct values, not the values where the aggregate is greater than 1. This is the DAX I have so far:

DEFINE
TABLE UserGroup = 
    SUMMARIZECOLUMNS (
        User[User],
        User[Branch],
        User[Region],
        "UserCount", COUNT('User'[User]),
        "User Distinct Count", DISTINCTCOUNT('User'[User])
        )
        
EVALUATE
    GROUPBY(
        UserGroup,
        UserGroup[Region],
        "Failing Formula", SUMX(CURRENTGROUP(), [User Distinct Count])
        
        )

userLOTh1 · Answer

EVALUATE


VAR allValTbl= -- DISTINCT(tbl)
        SUMMARIZE(
            tbl
            ,tbl[Region]
            ,tbl[District]
            ,tbl[User]
        )


VAR moreThenOne=
        FILTER(
            allValTbl
            ,CALCULATE(COUNTROWS(tbl))>1
        )
RETURN
    ADDCOLUMNS(
        VALUES(tbl[Region])
        ,"@DCount",VAR currReg = [Region] 
                    RETURN
                    COUNTROWS(
                            FILTER(
                                moreThenOne
                                ,[Region]=currReg
                            )
                    )+0
    )

Creating a DAX Measure to count where a subgroup is greater than 1

1 Answers