Forum Discussion

sgannon1's avatar
sgannon1
Frequent Visitor
10 years ago
Solved

Report not capturing all relevant data

Hi all,    Apologies if my question is a bit long winded, it's a little difficult to explain my issue.    I imported 6 tables from sql for my report, each giving different information on accounts...
Need Help
  • sdjensen's avatar
    sdjensen
    10 years ago

    I have build SSAS multidimentional models with implemented currency conversion, but I still haven't done this in DAX.

     

    However what you should need is you Currency Exch Rate table where (if data exists in your Dynamics NAV) you should be able to find your exchangerate with a from date. I am guessing you source data have some sort of posting date, so you need some DAX code that will lookup the exchangerate that was current when the transaction was made and then multiply with your amount. If you append this table to you already existing one then you should properly need an exchange rate (= 1 assuming that you current date is EURO) for this data too or your measure will break.

     

    I think you should try to open a new thread with your currency problem and provide a good description of your data then I am sure some of the DAX experts here will be able to provide you with a formula. 

sdjensen's avatar
sdjensen
Solution Sage
10 years ago

You should have add a table with all you accounts to you model and this table should also hold the current salesperson assigned to the account. Then create links from you table with your values to the account table and when you use salesperson from your account table in a visual this will show your values for each current salesperson to the account.

  • sgannon1's avatar
    sgannon1
    Frequent Visitor
    10 years ago

    Thanks for your suggestion.

    I already have a separate table imported with current salesperson and am using this when building my report. However the report still seems to be looking up the salesperson that is associated with an invoice, and not the one that is currently assigned to an account.  

    When I created the relationships between the tables I used invoice number as this was the only common trait. Could this be the reason it is now looking up salesperson by invoice?

    • sdjensen's avatar
      sdjensen
      Solution Sage
      10 years ago

      sgannon1 I would guess that is exactly why. I would create the account table with their current salesperson and then create the link with account and not salesperson - if you link with salesperson you will get your values split by the salesperson at the time the transaction was created.

    • v-qiuyu-msft's avatar
      v-qiuyu-msft
      Community Support
      10 years ago

      Hi sgannon1,

       

      In your scenario, all the data display in the report depends on the relationship between data tables. You can drag salesperson, invoice and account in a table visual to see what's the relationship between those fields, then you can know how to improve the data model. If you prefer, you can share some sample data and expected results for our analysis.

       

      If you have any question, please feel free to ask.

       

      Best Regards,
      Qiuyun Yu

      • sgannon1's avatar
        sgannon1
        Frequent Visitor
        10 years ago

        Hi v-qiuyu-msft,

        I have attached a file showing current relationships that I created between the tables.  The second relationship (Document No and No) is relating to invoice numbers. This is the only common trait between these tables so I don't think there is anything else I can link them with. 

        The contact table (from the first relationship) is the table that has the current salesperson details, so I have created a relationship between that and the sales invoice header (which has invoice details). 

        Sales Invoice line has the actual amount that was invoiced. 

        The sales invoice header table (below) also has details of the salesperson associated with the invoice, so I think this is still being picked up when I create reports.

         

        .

         

         

         

        Relationship Table:

         

         

        Contact table showing current salesperson assigned to account -