Forum Discussion

thorpyuk's avatar
thorpyuk
Icon for Helper III rankHelper III
6 years ago
Solved

Count unique orderers

Hi All, I have a sales table, and a site table, linked on [TerminalID] which is the till number. I want to count the unique ordering sites for each individual product that might be ordered... [Prodc...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    You can create the relationship in the Model pane of in Power BI (see image).

     

     

    If you are unable to create the relationship, then your measure will need to use the LOOKUPVALUE. Try this:

    Ordering Sites = 
    COUNTROWS(
        SUMMARIZE(
            ADDCOLUMNS(
                'Sales',
                "SiteID", LOOKUPVALUE(Terminal[SiteID], Terminal[TerminalID], Sales[TerminalID])
            ),
            [SiteID]
        )
    )

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Your table db_v_LookupSite holds the association between SiteCodes and TerminalIDs so you'll need to use that.

What is the primary key in db_v_LookupSite? I assume it is TerminalID, and db_v_LookupSite can have muiltiple rows for the same SiteCode (because I assume a Site can have >1 Terminal)?

Or is there only 1 row in db_v_LookupSite for each SiteCode, and each Site has only one TerminalID?

 

 

thorpyuk's avatar
thorpyuk
Icon for Helper III rankHelper III
6 years ago

Hi Eyles, i really appreciate you tracking back on this! Yes TerminalID is the primary key, and you are correct, each SiteCode exists multiple times on the 'db_v_LookupSite' table with multiple terminalIDs. The syntax below does evaluate, but returns the same result as DISTINCTCOUNT( 'db_v_SalesProduct'[TerminalId] ) as its counting the terminals still:

 

CountOrds = 
    COUNTROWS(
        SUMMARIZE(
        'db_v_SalesProduct',
     db_v_SalesProduct[TerminalID],
        "SiteCode", LOOKUPVALUE('db_v_LookupSite'[SiteCode], 'db_v_LookupSite'[TerminalID], 'db_v_SalesProduct'[TerminalID])
   )
)