linking sales targets to ledger in NAV

[danmone]

Hi 

I'm trying to link a spreadsheet with sales targets to my General Ledger table.  I can link it by date or by customer, but not both.  If I link by date it pulls in targets for all customers for that date, if I link it by customer it brings in all values for that customer for the whole year.  any ideas

[v-jayw-msft]

Hi @danmone ,

You might use USERELATIONSHIP() function.

Please check the following documents.

https://docs.microsoft.com/en-us/dax/userelationship-function-dax.

https://docs.microsoft.com/en-us/power-bi/desktop-relationships-understand#relevant-dax-functions.

And check the blog below about using multiple connections between tables.

https://powerbi.tips/2017/07/use-multiple-connections-between-tables/.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @danmone ,

would the following approach fir with your reporting requirements?

Don't link the General Ledger table and the sales target table directly.

Instead link only between dimension tables and fact tables with these relationships:

• A single to many relationship from customers table to general ledger table
• A single to many relationship from customers table to sales targets table
• A single to many relationship from date table to general ledger table
• A single to many relationship from date table to sales targets table

Asuming both, general ledger and sales targets have granularity daily, that's all. If you have different granularity, e.g. monthly targets, some adaption is needed.

BR

Martin