Forum Discussion

gtamir's avatar
gtamir
Icon for Post Patron rankPost Patron
8 years ago
Solved

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...
Need Help
  • prateekraina's avatar
    prateekraina
    8 years ago

    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's avatar
gtamir
Icon for Post Patron rankPost Patron
8 years ago

Thank you, how to attach a file?

prateekraina's avatar
prateekraina
Icon for Memorable Member rankMemorable Member
8 years ago

gtamir

 

Just upload on OneDrive / Dropbox and then share the link so that I can download.
You can also email it to me on [email protected]

 

Prateek Raina

  • prateekraina's avatar
    prateekraina
    Icon for Memorable Member rankMemorable Member
    8 years ago

    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's avatar
      gtamir
      Icon for Post Patron rankPost Patron
      8 years ago

      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's avatar
        prateekraina
        Icon for Memorable Member rankMemorable Member
        8 years ago

        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