Forum Discussion
Post PatronGetting 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
Post PatronWhat 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
Memorable MemberWhat 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.