Forum Discussion
Report not capturing all relevant data
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.
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 -
sgannon1 - This look like Dynamics NAV data and I know that database very well - I have been building BI models on top of Dynamics NAV for 10 years.
1. Why do you go though Sales Invoice Header to link to Contact? You have the Sell-to and Bill-to customer right there on the Sales Invoice Line. ?
2. Do your customer numbers match your contact numbers? else you would need to create a relationship to you customer table instead of your contact table.
3. With the current relationships if you add Salesperson from your contact table to your visual and your invoice amount from Sales Invoice Line then this will be split by the current Salesperson from the contact if your customer numbers match your contact numbers.
Attached screenshot is from a model I build on Dynamics NAV demo data - all measure names and dimensions is in danish, but it basically show Revenue, Gross Profit and Gross Margin per month, year to date, last year to date, by country, customer and item. The model also offer the option to split the sales measures by sales person either the sales person from the time of the transaction or current sales person attached to the customer.
4. Why don't you use your customer table instead of contact table? customer table should also have the current salesperson and then create a direct connection from Sales Invoice Line to Customer?
Hi sdjensen,
Thanks so much for your help. Yes you're right it is Navision data. I am very much a beginner to working with databases and Power BI, but you have really helped. I replaced the contact table with the customer one, and linked this to the sales invoice header, and it seems to be capturing the data correctly now.
I also need to add another table to my data (I was planning to append the table), but the currency is currently in GBP and I need to convert to EURO. Do you have experience of doing this?
Thanks again for your help.
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.