cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Hi Guys

I need to find out the following please;

Reference Table;

 Customer Number MRDR number Price Valid from: Price Valid to: Promo NIV/Case 11111 12345 10/01/2019 15/02/2019 0.42 22222 12346 7/07/2019 10/08/2019 0.82 33333 12347 20/02/2019 19/03/2019 0.12

Table 2

 Customer  L8 Pricing entry date Material=mrdr Subtotal 4= NIV 11111 21.03.2019 12345 0.50 22222 08.07.2019 12346 0.80 33333 20.04.2019 12347 0.43

How can I find out the customer L8 in Table 2 have  values in column " Subtotal 4= NIV" exist in referance table for the same customer between "Price Valid from:" and "Price Valid to:". and if doesn't exisit it through "ERROR" in the row in the result.

Thank you,

2 ACCEPTED SOLUTIONS
Community Support

Hi @Anonymous ,

We can create a calculated table to get the matched value:

``````Column =
CALCULATE (
MAX ( 'Table 2'[Subtotal 4 = NIV] ),
FILTER (
'Table 2',
'Table 2'[Customer L8] = [Customer Number ]
&& 'Table 2'[Pricing entry date] >= [Price Valid from]
&& 'Table 2'[Pricing entry date] <= [Price Valid to]
)
)``````

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Note your query in table 1 (reference table) is not used in the query to link the two tables

= CALCULATE
( MAX
( 'Table 2'[Subtotal 4 = NIV] ), FILTER ( 'Table 2', 'Table 2'[Customer L8] = [Customer Number ]
&& 'Table 2'[Pricing entry date] >= [Price Valid from] && 'Table 2'[Pricing entry date] <= [Price Valid to]
&& 'Table 2'[Pricing entry date] = TODAY() + 1 ) )

Thank you,

5 REPLIES 5
Community Support

Hi @Anonymous ,

We can create a calculated table to get the matched value:

``````Column =
CALCULATE (
MAX ( 'Table 2'[Subtotal 4 = NIV] ),
FILTER (
'Table 2',
'Table 2'[Customer L8] = [Customer Number ]
&& 'Table 2'[Pricing entry date] >= [Price Valid from]
&& 'Table 2'[Pricing entry date] <= [Price Valid to]
)
)``````

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

V-lid-msft,

Please how about if I want to look in column "Pricing entry date" for tomorrow dates only if they exist.

For example if today is 29.01.2020, I want the script to look only for 30.01.2020 between start date and end date from the referance file.

Thank you,

Community Support

Hi @Anonymous ,

``````Column =
CALCULATE (
MAX ( 'Table 2'[Subtotal 4 = NIV] ),
FILTER (
'Table 2',
'Table 2'[Customer L8] = [Customer Number ]
&& 'Table 2'[Pricing entry date] >= [Price Valid from]
&& 'Table 2'[Pricing entry date] <= [Price Valid to]
&& 'Table 2'[Pricing entry date] = TODAY() + 1
)
)``````

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Note your query in table 1 (reference table) is not used in the query to link the two tables

= CALCULATE
( MAX
( 'Table 2'[Subtotal 4 = NIV] ), FILTER ( 'Table 2', 'Table 2'[Customer L8] = [Customer Number ]
&& 'Table 2'[Pricing entry date] >= [Price Valid from] && 'Table 2'[Pricing entry date] <= [Price Valid to]
&& 'Table 2'[Pricing entry date] = TODAY() + 1 ) )

Thank you,

Impactful Individual

Hi there,

Your column names got me a little confused I think.

You are working with 2 tables? You have a table per customer?

How does the customer Number come in to play? L8?

Regards,

Robbe

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.