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]
        )
    )

     

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:

  • 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

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

    I've given you the Kudos, thanks again 🙂