Forum Discussion
Getting data from another table to visualization
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
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
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.