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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
gtamir
Post Patron
Post Patron

Getting data from another table to visualization

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?

ScreenHunter_061.jpg

1 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

View solution in original post

16 REPLIES 16
prateekraina
Memorable Member
Memorable Member

Hi @gtamir,

 

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

 

Prateek Raina

ScreenHunter_062.jpg

ScreenHunter_063.jpg

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

Thank you, how to attach a file?

@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

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

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.

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

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

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

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.

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:
6.png

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:

7.PNG

 

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:
4.PNG
5.PNG

 

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:
2.PNG
3.PNG

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

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

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.

OK, It is clear now.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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