CODEWITHSUNDEEP
powerbidax
Zabman
Zabman

Reputation: 117

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

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])
        
        )

Upvotes: -1

Views: 159

Answers (1)

Mik
Mik

Reputation: 2103

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
    )

Upvotes: 1

Related Questions