Forum Discussion

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

prateekraina's avatar
prateekraina
Memorable Member
8 years ago

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

    Thank you, how to attach a file?

    • prateekraina's avatar
      prateekraina
      Memorable 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
        Memorable 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