Forum Discussion
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...
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] ) )
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:
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]
)
)
EylesIT, thank you so much! THat did work, with a bit of tweaking!
I've given you the Kudos, thanks again 🙂