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, there's no terminal table though...

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?

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    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 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])
   )
)

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Happy to help - I ask questions on here as well!

     

    If TerminalID is the primary key, then db_v_LookupSite is can be a Terminal/Site/TerminalSite dimension table for the Sales fact table. Have you created a relationship between db_v_LookupSite and Sales on the TerminalID field?

  • thorpyuk's avatar
    thorpyuk
    Helper III
    6 years ago

    Hi Eyles, it's not my dataset, i'm only a consumer - i can create measures but can't see the underlying links.

    It looks like TerminalID is the linked field - i can't see anything else that would be:

  • thorpyuk's avatar
    thorpyuk
    Helper III
    6 years ago

    EylesIT, thank you so much! THat did work, with a bit of tweaking!

    I've given you the Kudos, thanks again 🙂