Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hey all,
I need help displaying something correctly in my matrix visual. I have two tables--
1) Users Tbl
2) Domains Tbl
I need to display a matrix visual like the following:
User | Domain Count | Top Domain |
some user | 25 | google.com |
another user | 15 | yahoo.com |
TOTAL | 40 | google.com |
By default, the matrix visual displays the first or last domain. Can anyone help me create a measure that will output the Domain based on the Domain Count? The total should reflect the entire dataset irrespective of user, but by row it should show the top domain by user. Hope that makes sense!
Any help is appreciated.
Solved! Go to Solution.
Your measure is missing a CALCULATE for the COUNT expression in the variables vUserDomain and vAllDomain. Alternatively, you can use a measure, resulting in an implicit CALCULATE. The correct measures are below:
Domain Count = COUNT ( Domains[Domain] )
Top Domain =
VAR vUser =
MAX ( Users[UserId] )
VAR vUserDomain =
ADDCOLUMNS (
SUMMARIZE ( Domains, Domains[UserId], Domains[Domain] ),
"DomainCount", [Domain Count]
)
VAR vUserDomainRank =
ADDCOLUMNS (
vUserDomain,
"DomainRank", RANKX ( vUserDomain, [DomainCount],, DESC, DENSE )
)
VAR vUserTopDomainRow =
FILTER ( vUserDomainRank, [DomainRank] = 1 )
VAR vUserTopDomain =
MAXX ( vUserTopDomainRow, Domains[Domain] )
VAR vAllDomain =
ADDCOLUMNS (
SUMMARIZE ( Domains, Domains[Domain] ),
"DomainCount", [Domain Count]
)
VAR vAllDomainRank =
ADDCOLUMNS (
vAllDomain,
"DomainRank", RANKX ( vAllDomain, [DomainCount],, DESC, DENSE )
)
VAR vAllTopDomainRow =
FILTER ( vAllDomainRank, [DomainRank] = 1 )
VAR vAllTopDomain =
MAXX ( vAllTopDomainRow, Domains[Domain] )
RETURN
IF ( HASONEVALUE ( Users[UserName] ), vUserTopDomain, vAllTopDomain )
Proud to be a Super User!
Hi @StephenK ,
Could you please provide some sample data of table Users and Domains and your desired result with examples? Whether exist any relationship between these two tables? If yes, could you please also provide the relationship field? By the way, Domain Count is the number of users grouped by domain? User field display the specific user name?
Best Regards
Rena
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.
@DataInsights @v-yiruan-msft Here is the sample data.. there is a 1 to many relationship on the UserId column. Domain count should be the count of VisitId by domain.
UserId UserName
1 | John Smith |
2 | Jane Doe |
3 | Sally May |
4 | Roy Rogers |
5 | Sam Brown |
VisitId UserId Domain
1 | 1 | google.com |
2 | 1 | facebook.com |
3 | 1 | google.com |
4 | 1 | google.com |
5 | 2 | twitter.com |
6 | 2 | twitter.com |
7 | 2 | facebook.com |
8 | 3 | adp.com |
9 | 3 | yahoo.com |
10 | 4 | google.com |
11 | 4 | google.com |
12 | 5 | facebook.com |
13 | 5 | facebook.com |
Here's the result I get. The second visual shows that google.com has the most visits, which is what displays on the total row of the first visual. Is this the correct result?
Proud to be a Super User!
@DataInsights that is the correct result in your screenshots, but for some reason it's not what i'm getting when I implement the formula in my project file. The totals row is showing the domain with the least number of visits. I've tried tweaking the formula in different ways just to see how the output changes. Changing the sort order of RANKX does nothing. Changing MAXX to MINX indicates something interesting.
MAXX is currently pulling the very last domain in the table with 1 visit. Changing to MINX pulls the first domain in the table with 1 visit. I can't figure out why the formula keeps pulling domains with the lowest visit counts.
Would you be able to provide a sanitized version of your pbix? You can remove everything unrelated. If not, would you post your measure?
Proud to be a Super User!
@DataInsights Here you go: TopDomains.pbix (OneDrive)
I've included a screen shot as well which shows the problem. Blue = correct value. Red = Incorrect. The visuals on the right are a reference on what the correct output should be. Thanks for your help!
Your measure is missing a CALCULATE for the COUNT expression in the variables vUserDomain and vAllDomain. Alternatively, you can use a measure, resulting in an implicit CALCULATE. The correct measures are below:
Domain Count = COUNT ( Domains[Domain] )
Top Domain =
VAR vUser =
MAX ( Users[UserId] )
VAR vUserDomain =
ADDCOLUMNS (
SUMMARIZE ( Domains, Domains[UserId], Domains[Domain] ),
"DomainCount", [Domain Count]
)
VAR vUserDomainRank =
ADDCOLUMNS (
vUserDomain,
"DomainRank", RANKX ( vUserDomain, [DomainCount],, DESC, DENSE )
)
VAR vUserTopDomainRow =
FILTER ( vUserDomainRank, [DomainRank] = 1 )
VAR vUserTopDomain =
MAXX ( vUserTopDomainRow, Domains[Domain] )
VAR vAllDomain =
ADDCOLUMNS (
SUMMARIZE ( Domains, Domains[Domain] ),
"DomainCount", [Domain Count]
)
VAR vAllDomainRank =
ADDCOLUMNS (
vAllDomain,
"DomainRank", RANKX ( vAllDomain, [DomainCount],, DESC, DENSE )
)
VAR vAllTopDomainRow =
FILTER ( vAllDomainRank, [DomainRank] = 1 )
VAR vAllTopDomain =
MAXX ( vAllTopDomainRow, Domains[Domain] )
RETURN
IF ( HASONEVALUE ( Users[UserName] ), vUserTopDomain, vAllTopDomain )
Proud to be a Super User!
Try this measure:
Top Domain =
VAR vUser =
MAX ( Users[User ID] )
VAR vUserDomain =
ADDCOLUMNS (
SUMMARIZE ( Domains, Domains[User ID], Domains[Domain] ),
"DomainCount", Domains[Domain Count]
)
VAR vUserDomainRank =
ADDCOLUMNS (
vUserDomain,
"DomainRank", RANKX ( vUserDomain, [DomainCount],, DESC, DENSE )
)
VAR vUserTopDomainRow =
FILTER ( vUserDomainRank, [DomainRank] = 1 )
VAR vUserTopDomain =
MAXX ( vUserTopDomainRow, Domains[Domain] )
VAR vAllDomain =
ADDCOLUMNS (
SUMMARIZE ( Domains, Domains[Domain] ),
"DomainCount", Domains[Domain Count]
)
VAR vAllDomainRank =
ADDCOLUMNS (
vAllDomain,
"DomainRank", RANKX ( vAllDomain, [DomainCount],, DESC, DENSE )
)
VAR vAllTopDomainRow =
FILTER ( vAllDomainRank, [DomainRank] = 1 )
VAR vAllTopDomain =
MAXX ( vAllTopDomainRow, Domains[Domain] )
RETURN
IF ( HASONEVALUE ( Users[User Name] ), vUserTopDomain, vAllTopDomain )
Proud to be a Super User!
Thanks for this @DataInsights! It seems to be working at the row level, but the domain displayed on the total line is incorrect. It appears to be showing the domain with the lowest domain count. I tried changing the sort on the RANKX functions to ASC just to see if that fixed it, but it did not
Would you be able to provide some sample data (that I can copy/paste) that illustrates the issue? It's working correctly for the sample data I created.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |