Forum Discussion

StephenK's avatar
StephenK
Resolver I
5 years ago
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...
Need Help
  • DataInsights's avatar
    DataInsights
    5 years ago

    StephenK,

     

    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 )

     

     

DataInsights's avatar
DataInsights
Super User
5 years ago

StephenK,

 

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?

 

 

StephenK's avatar
StephenK
Resolver I
5 years ago

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.

  • DataInsights's avatar
    DataInsights
    Super User
    5 years ago

    StephenK,

     

    Would you be able to provide a sanitized version of your pbix? You can remove everything unrelated. If not, would you post your measure?

    • StephenK's avatar
      StephenK
      Resolver I
      5 years ago

      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!

      • DataInsights's avatar
        DataInsights
        Super User
        5 years ago

        StephenK,

         

        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 )