Forum Discussion

thorpyuk's avatar
thorpyuk
Helper 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]
        )
    )

     

thorpyuk's avatar
thorpyuk
Helper III
6 years ago

Hi EylesIT, yes that's exactly what i want to do! I'm close with that code you posted - i've edited it to the correct table names, but i must have missed something, or misunderstood some of your code, as it's counting the total terminals rather than the total sites.

Here's what i've done:

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

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Oops! THis should do it:

 

Ordering Sites =
    COUNTROWS(
        SUMMARIZE('Sales',
        'Terminal'[SiteID]
        )
    )