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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
andrea_chiappo
Helper III
Helper III

distinctcount across related tables with conditions and non uniform structure

What I'm trying to do is pretty involved (to me at least), so this might be interesting to the more experienced solutioners  

 

I have (amongst others) the following three table:  

DimGroups[groupID] <-> DimGroupsUsers[groupID, userID] <-> DimUsers[userID]  

 

where the arrows indicate the (active) relationships and the values in brackets the relating dimensions  

(with slight misuse of the central one, but I guess you'll get the point)  

 

Below snapshots of the first two tables filtered for customerID=20  

 

DimGroups

Capture_LI (3).jpg

and DimGroupsUsers

Capture2.PNG

 

For all customers there is at least one fathergroup and several groups (20_1 and 20_2 in the example above)   

I know (from DimGroups) that a group is a fathergroup if it has no fathergroupID (20_1, 20_2). 

 

From DimGroupsUsers I can calculate the number of users inside each group or context. But there are some caveats  

 

All groups always belong to at least one fathergroup,

Users may  belong to one or multiple groups and the corresponding fathergroup, or  

Users may belong to a group but not a fathergroup and viceversa  

the snapshots above depict an example of the latter case: 

users 20_3, 20_5 and 20_8 do not belong to any group but only to fathergroups,

groups 20_4, 20_6, 20_7, 20_10, 20_11, 20_13, 20_14 are empty and 

fathergroup 20_1 contains three users and fathergroup 20_2 contains 7 users (the top 7 rows of DimGroupsUsers)

 

I want to calculated is the number of users inside each group and fathergroup  

Ideally, I want to be able to display this using the Sunburst visual (a multi-layer donout) 

 

Here's what I did:  

first I created the inGroup column in DimGroupsUsers with the following formula:  

 

inGroup = IF(
    ISBLANK(
        LOOKUPVALUE(
            DimGroups[fatherGroupID],
            DimGroups[groupID],
            DimGroupsUsers[groupID])),
    false,
    true)

 

Then, in DimGroups, I thought of calculating the number of users inside each group or fathergroup 

with a calculated column using the following formula:  

 

GroupsPopulation = IF(  
    ISBLANK(DimGroups[fatherGroupID]),  
    CALCULATE(  
        DISTINCTCOUNT(DimGroupsUsers[userID]),  
        FILTER(DimGroups, SELECTEDVALUE(DimGroups[groupID])),  
        FILTER(DimGroupsUsers, NOT(DimGroupsUsers[inGroup]))  
    ),  
    IF(  
        CONTAINS(  
            DimGroupsUsers,  
            DimGroupsUsers[groupID],  
            DimGroups[groupID]  
        ),  
        CALCULATE(  
            DISTINCTCOUNT(DimGroupsUsers[userID]),  
            FILTER(DimGroups, SELECTEDVALUE(DimGroups[groupID]))  
            ),  
        0  
    )  
)  
 
I was expecting the new column to show the sum of users in each group or fathergroup   
corresponding to each row of DimGroups  
 
However, I only get zeros in correspondance of the empty groups  
NOTE: I added the SELECTEDVALUE because initially IF was not filtering by custermID context  
 
Can anybody help me solve this conundrum? Many thanks
1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

I could be mistaken but perhaps try:

 

GroupsPopulation = IF(  
    ISBLANK(DimGroups[fatherGroupID]),  
    CALCULATE(  
        DISTINCTCOUNT(DimGroupsUsers[userID]),  
        FILTER(DimGroups, DimGroups[groupID]=EARLIER(DimGroups[groupID])),  
        FILTER(DimGroupsUsers, NOT(DimGroupsUsers[inGroup]))  
    ),  
    IF(  
        CONTAINS(  
            DimGroupsUsers,  
            DimGroupsUsers[groupID],  
            DimGroups[groupID]  
        ),  
        CALCULATE(  
            DISTINCTCOUNT(DimGroupsUsers[userID]),  
            FILTER(DimGroups, DimGroups[groupID] = EARLIER(DimGroups[groupID]))  
            ),  
        0  
    )  
)  

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

I could be mistaken but perhaps try:

 

GroupsPopulation = IF(  
    ISBLANK(DimGroups[fatherGroupID]),  
    CALCULATE(  
        DISTINCTCOUNT(DimGroupsUsers[userID]),  
        FILTER(DimGroups, DimGroups[groupID]=EARLIER(DimGroups[groupID])),  
        FILTER(DimGroupsUsers, NOT(DimGroupsUsers[inGroup]))  
    ),  
    IF(  
        CONTAINS(  
            DimGroupsUsers,  
            DimGroupsUsers[groupID],  
            DimGroups[groupID]  
        ),  
        CALCULATE(  
            DISTINCTCOUNT(DimGroupsUsers[userID]),  
            FILTER(DimGroups, DimGroups[groupID] = EARLIER(DimGroups[groupID]))  
            ),  
        0  
    )  
)  

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Thanks for the solution Greg.  
Unfortunaltely I just realised that the solution works well for the example case 

I presented in the original post, but not for others.  

 

For example, in the opposite case, where all users are in the same fathergroup,  

the formula double counts the users in the fathergroup  

Capture.PNG

(here Context is the company's definition of fathergroup)  

 

In this situation, the double donout chart will display a fathergroup 

containing twice as many users and a separate Context group containing  

exactly the same number of users as in the other groups, while the 

Context group is supposed to be a dummy group collecting all users  

not assigned to a fathergroup  

 

Capture2.PNG  

To remedy this, I thought of doing the following:  

adding a measure in the DimGroupsUsers table  

to calculate the number of users inside groups, using the following 

UsersInGroupsRole = CALCULATE(
    IF(
        DISTINCTCOUNT(DimGroupsUsers[userID])=0,
        0,
        DISTINCTCOUNT(DimGroupsUsers[userID])
    ),
    ALLEXCEPT(DimGroupsUsers, DimGroupsUsers[userID]),
    FILTER(DimGroupsUsers, DimGroupsUsers[inGroup]),
    USERELATIONSHIP(DimGroupsUsers[userID], DimUsers[userID])
)
 

 and then modify the solution like this  

Populations =
    var contpop = CALCULATE(
        IF(
            DISTINCTCOUNT(DimGroupsUsers[userID])=0,
            0,
            DISTINCTCOUNT(DimGroupsUsers[userID])
        ),
        FILTER(ALLNOBLANKROW(DimGroups), DimGroups[groupID]=EARLIER(DimGroups[groupID])),
        FILTER(ALLNOBLANKROW(DimGroupsUsers), NOT(DimGroupsUsers[inGroup]))
    )
    var groupop = CALCULATE(
        IF(
            DISTINCTCOUNT(DimGroupsUsers[userID])=0,
            0,
            DISTINCTCOUNT(DimGroupsUsers[userID])
        ),
        FILTER(ALLNOBLANKROW(DimGroups), DimGroups[groupID]=EARLIER(DimGroups[groupID])),
        FILTER(ALLNOBLANKROW(DimGroupsUsers), DimGroupsUsers[inGroup])
    )
    return IF(
        ISBLANK(DimGroups[fatherGroupID]),
        IF(contpop <> [UsersInGroupsRole], contpop, 0),
        IF(
            CONTAINS(
                DimGroupsUsers,
                DimGroupsUsers[groupID],
                DimGroups[groupID]
            ),
        groupop,
        0
    )

)  

 

 However, I get the following error message:  

A circular dependency was detected: DimGroups[Populations]  

 

Any idea how to solve this? Thank you very much

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.