Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
and DimGroupsUsers
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:
Then, in DimGroups, I thought of calculating the number of users inside each group or fathergroup
with a calculated column using the following formula:
Solved! Go to Solution.
I could be mistaken but perhaps try:
I could be mistaken but perhaps try:
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
(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
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
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
142 | |
71 | |
64 | |
52 | |
50 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |