Calculating a count across disconnected tables

[jackj]

Hi,

I am struggling to do what I think should be a very simple calculation.  I have two disconnected tables, Orders and Visits.  Each of them shares a similar UserID column.  It's called 'CustomerID' in Orders and 'UserID' in visits.  Most values are the same, however not all IDs appear in both columns and I am unable to create a relationship between the two tables.

All that I am looking to do is create a calculated measure in the "Visits" column which counts the number of times each 'UserID' in visits appears in the 'CustomerID' column in Orders.  This seems to be a very simple ask, but I am struggling to make this work in PowerBI.  Does anyone have any suggestions?

Thanks in advance.

[parry2k]

@jackj same thing, just use different calculation

Count = 
CALCULATE (
SUM ( Customers[Sales] ),
TREATAS ( VALUES ( Visits[UserId] ), Customer[CustomerId] )
)

[parry2k]

@jackj sorry not sure what is your question?

[jackj]

In addition to counting, I want to be able to sum the sales value in "Orders" that corresponds to the selected user ID.  Hope that clarifies.

[parry2k · Accepted solution]

@jackj usually in scenarios like this you should have a common dimension table with a unique user id and link these two together, anyhow try the following expression to get the count:

Count = 
CALCULATE (
COUNTROWS ( Customer ),
TREATAS ( VALUES ( Visits[UserId] ), Customer[CustomerId] )
)

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[jackj]

Thank you!  This worked for the count.  One other question - how could I do something similar to be able to do a sumif-like function rather than a count with this same data, i.e., taking the sum of sales in the "orders" table based on the userID from the "visits" table?