Create a Calculated Column from Different Tables with Logic statements

[Crowfield]

Hi all,

I have a rather interesting question. What I want to do is to create a Customer Price List with Dynamics NAV data.

That means the output is a list with Items with a Customer Unit Price, based on different variables (like Line Discount or specific price). The input is a filter on customer.

So basically I want to calculate a new column, based on multiple tables with some logic statements. But how should I translate that to a DAX statement?

I tried to put the architecture of the tables and the relevant fields below. Hopefully someone can help me out. 

Table Customer

• Customer No
• Customer Name
• Customer Discount Group

Table Item

• Item No
• Item Discount Group
• Unit Price

Table Sales Line Discount

• Sales Type [Customer or Customer Discount Group]
• Sales Code [Customer No or Customer Discount Group Code]
• Type [Item or Item Discount Group]
• Code [Item No or Item Discount Group Code]
• Starting Date
• Ending Date
• Line Discount %

Table Sales Prices

• Item No
• Sales Type
• Sales Code
• Unit Price

The filter is set on Customer.

The output is a list with items, with a calculated customer price.

The Field CustomerPrices is a new calculated field (new column).

The logic is:

• CustomerPrice of Item is Unit Price - Line Discount
• Or if for this Item exsist in the Table Sales Price, then that is the Customer Price
• Line Discount is calculated form the table Sales Line Discount based on:
• The customer is lised directly in the table with a Line Discount (prio 1)
• The customer is part of a Customer Discount Group, which code is listed in the table with a Line Discount (prio 2)
• The most recent starting date is valid

[Crowfield]

Is it possible to move the topic to the DAX section?

It was ment to be posted there, but somehow ended up here.

Thanks a lot.

[Eric_Zhang]

@Crowfield

Your requirement is not quite clear, would you mind posting any sample data and expected output?