Forum Discussion
StephenK
5 years agoResolver I
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...
- 5 years ago
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 )
StephenK
5 years agoResolver I
DataInsights Anonymous 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 |
DataInsights
5 years agoSuper User
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?