Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
thorpyuk
Helper III
Helper III

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... [Prodcode] is my product ID

Thanks in advance!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

 

PowerBI Test 2.PNG

 

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

 

View solution in original post

18 REPLIES 18
thorpyuk
Helper III
Helper III

Anyone any other suggestions?

@thorpyuk ,

 

Have you tried to count by the id for the site table ?

 

Something like:

 

DISTINCTCOUNT(ID_SITE_TABLE)

 

Ricardo



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Hi Camargos88, yes that just gives me the total number of distinct sites (56) against each product, but i want only the number of those sites that have ordered

Temp.jpg

@thorpyuk ,

 

Can you provide some data as example ?

 

Ricardo



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Hi Camargos, here's the layout of my sales table ( db_v_SalesProduct ) and my site table ( db_v_LookupSite ).

They appear to link on the [TerminalID] field, but as i say, each site will have multiple terminals.

How do i share a file with data?

Temp.jpg

 

@thorpyuk ,

 

You can use onedrive, google drive, dropbox...

 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Anonymous
Not applicable

Ah ok, I see. So each SiteID can have 1 or more TerminalIDs, and you want the measure to return the number of distinct SiteIDs for each ProdCode. Try this

 

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

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
Not applicable

Oops! THis should do it:

 

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

Hi EylesIT, there's no terminal table though...

Anonymous
Not applicable

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?

 

 

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
Not applicable

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?

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:

Temp.jpg

Anonymous
Not applicable

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

 

PowerBI Test 2.PNG

 

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 🙂

Anonymous
Not applicable

Create a measure with this formula:

Ordering Sites = COUNTDISTINCT('Sales'[TerminalID])

Then add this measure to a table or matrix visual. When you also add ProdCode to the visual, the distinct count will be broken down by ProdCode.

Hi EylesIT, thanks for your reply - that does give me unique terminals, but each site may have 3-4 different terminalID's... i wanted unique sites

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.