Forum Discussion
Solved
Matrix Visual Display Top Domain by Count
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 Doma...
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 )
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 )
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.