Skip to main content
cancel
Showing results for
Search instead 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

@v-lid-msft , Thank you for your reply, please i noticed you didnt use referance table in your query. when I applied your requery I get an error.

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,

Sarmad Dara

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,

thank you for your solution.

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.

Can you help please?

Thank you,

Sarmad Dara

Community Support

Hi @Anonymous ,

We can add a condition to meet your requirement:

``````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

@v-lid-msft , Thank you for your reply, please i noticed you didnt use referance table in your query. when I applied your requery I get an error.

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,

Sarmad Dara

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?

Could you provide some more information?

Regards,

Robbe

## Helpful resources

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.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors