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.

Power BI Monthly Update - August 2024

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

Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors