cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
razmochaev
Helper I
Helper I

Calculating N of visits for each caller

Hi everyone. Been struggling for a long time with the following task.

I have two fact tables:

1. factLeads, containing data for incoming calls and lead source. Some of the callers are our existing clients.

2. factVisits, containing data for actual visits to our clinic. 

 

I also have a bridge table that stores associations with phone numbers and client IDs. One client can have many phone numbers and one phone number can belong to many clients.

DataModel.png

I need to measure, for each call, the number of visits conducted during the next 30 days after the call.

 

As far as I can see it, the solution is based on the following steps:

1. For any given time period get the list of phone number

2. Get the client IDs from bridge table who have these phone numbers associated with them.

3. Filter the factVisits table with those client IDs.

 

Here's a sample file 

 

I will be very grateful for any suggestions.

6 REPLIES 6
v-zhangti
Community Support
Community Support

Hi, @razmochaev 

 

Can you provide simple PBIX files instead of Excel files? What do you expect the output to look like and can you show it with pictures?

 

Best Regards

lbendlin
Super User
Super User

That's not a bridge table, that is a dimension - in this case the phone number. Bridge tables would have arrows in two directions, at least on one side.

 

Try combining the dimLeadsPhones and dimClients tables.

Hi @lbendlin 

Sorry for bad quality screenshot of the data model, I've replaced it with a better one, clearly showing that bridgeClientsPhones is a bridge table connecting dimClients and dimLeadsPhones, as a client can have many phone numbers and a phone number can be attributed to many clients in our database.

"Bridge tables are dimensional tables needed to address the many to many relationships between facts and dimensions OR dimensions and multi-valued attributes you may come across when modeling your star schema."

That is an insufficient definition. A bridge allows the flow of something ( in this case filters) in one or both directions. In your data model no filter flow is possible in either direction.

Simply put, a bridge table is any table that relates one entity to another in order to define the many-to-many relationship between the entities. This is the most general definition of a bridge table. It has nothing to do with any filters, any models, any directions and so on. Such a bridge table will have at least 2 columns in which the keys to the said entities will be placed.


And therefore the table that @razmochaev calls "a bridge table" is indeed a correctly defined bridge table.

 

What is a bridge table in data warehouse? – idswater.com

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors