Getting data from another table to visualization

[gtamir]

Hi,

I have a table BestClubZikui 5-20181 which is the customer file, and Sales file.

In the visualization I build a table from the sales table to show the Pharmacy code, Total, Count of visits, and other fields.

I want to add from customers table the number of customers registered in this pharmacy.

This Messuere: No. of customers per Pharmacy = DISTINCTCOUNT('BestClubZikui 5-20181'[CustomerKey])

gives me the same number in all the lines. The relationship is one to many.

If I change the connection to BOTH it gives Blanks for all rows.

I tryed RELATED bu it does not accept it.

How can I put the data from customers in the visualisation?

[prateekraina]

Hi @gtamir,

Can you send a screenshot of your model in the relationships view?

Prateek Raina

[gtamir]

[gtamir]

[prateekraina]

Hi @gtamir,

The relationship seems fine to me. Ideally it should work.

Will it be possible to share the .pbix, i would like to dig deeper into this issue?

Prateek Raina

[gtamir]

Thank you, how to attach a file?

[prateekraina]

@gtamir

Just upload on OneDrive / Dropbox and then share the link so that I can download.
You can also email it to me on prateek.raina91@gmail.com

Prateek Raina

[prateekraina]

Hi @gtamir,

You were referencing wrong Table Name in the measure thats why you were getting same count for every row.

Here is the correct expression:

No. of customers per Pharmacy = DISTINCTCOUNT('BestClubSales 5-2018'[CustomerKey])

Prateek Raina

[gtamir]

This is not what I want. This table holds only the customers that bought in this period. I want to count from the customer file (BestClubZikui 5-2018) all the customers that assotiated with each Pharmacy Code.

Each month this file is growing and I want to show the increase of number of customers for each row.

You can see this in the second table of the visualization.

[prateekraina]

Hi @gtamir,

I do not see any matching Customerkey between BestClubSales 5-2018 and BestClubZikui 5-20181.

Are you sure you need to create relationship on this column or is the data right?

Since, there are no matching customers between these two tables, you will not be able to achieve this.

Will email you the excel in which I have compared the customerkey of both tables.

Prateek Raina

[gtamir]

You are right and I fixed it. Mixed the columns on Merge.

It does not matter to my problem because the program should count the number of customers for each PharmacyCode. This is an absolut number and (to my understanding) does not depends on other fields.

Sent you the correct file again.

Thanks Giora

[prateekraina · Accepted solution]

Hi @gtamir,

It matters in your case because you are joining on CustomerKey which has no matching values between the two tables so the inner join will result in 0 rows. 

I have to say this that please spend sometime on modelling data before you start creating measures and columns. Your main two tables have redundant columns which makes the third person wonder what actually is going on in the model. I would suggest you to remove unwanted columns to make the model clean.

I have solved the problem only keeping in mind that you need to count customers from BestClubZikui 5-20181 table for PharmacyCode in BestClubSales 5-2018.

Follow below steps:
1. Create a separate table which contains unique PharmacyCode from BestClubSales 5-2018 table. It can be done by using DAX or Power Query in QueryEditor. Here is the DAX expression for that:

DimPharmacyCode = DISTINCT(VALUES('BestClubSales 5-2018'[PharmacyCode]))

2. Create a relationship between DimPharmacyCode and BestClubZikui 5-20181 on Pharmacy Code column.

3. Use the PharmacyCode column in DimPharmacyCode in your visual table and remove the old one. You will get the desired count.

Sending you pbix of the same.

Prateek Raina

[gtamir]

Yes I see your solution. thank you.

But what do I learn from it? Is it that I need to filter a uniqe column? And what is the affect on the data from another table and not from the first table.

[prateekraina]

Hi @gtamir,

Here is a detailed explanation of what was happening in your model.

I have leveraged simple SQL Queries to make you understand what was wrong. 

Suppose I have two tables:
1. Sales [BestClubSales 5-2018 as per your model]

2. Customer [BestClubZikui 5-20181 as per your model]

Here is how they look:

Now, you are using CustomerKey to join the two tables as per your relationship in Power BI. So when i join these two tables in SQL on CustomerKey, here is how the table looks:

You see what is happening, since you used CustomerKey to join you will get below result when you count customer from Sales table against PharmacyCode of Customer table:

The above result is wrong you see. That's why you were getting same number for all rows in Power BI

Solution:

Now to resolve this problem you should join the tables using Pharmacy Code but since Power BI doesn't allow Many to Many relationship between two tables so we had to create another table which holds distinct set of Pharmacy Code so that we can create 1 to Many relationship. That did the trick.

Here is what happens when i change my join from CustomerKey to PharmacyCode in SQL:



That's what you wanted to achieve right ?

I hope I have made myself clear. Let me know if you have any follow up questions. Would be happy to answer.

Prateek Raina

[gtamir]

What a detailed explanation.

I have to insist on another question.

You sayed: 

"It matters in your case because you are joining on CustomerKey which has no matching values between the two tables so the inner join will result in 0 rows". 

After you made this remark I fixed this problem. Now the CustomerKey is correct and maches in both tables. in one table there are more customers then on the other, but this is OK I suppose. 

But yet it doesn't work if I connect the tables on CustomerKey.

Giora

[prateekraina]

You missed the whole point. Joining on customerkey itself is the issue when you want to group on PharmacyCode.
What I stated earlier was that when you don’t have any matching keys between two tables you anyway wont be able to achieve anything because inner join will return 0 rows.

However, since you wanted grouping on PharmacyCode then it was mandatory to join on PharmacyCode.
Now why I created a separate table because the granularity of the original table was Customerkey which means one PharmacyCode was associated to multiple customers so we were unable to join with original table.

[gtamir]

OK, It is clear now.