The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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,
Solved! Go to Solution.
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,
@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
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,
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
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,
@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
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
User | Count |
---|---|
122 | |
89 | |
75 | |
55 | |
45 |
User | Count |
---|---|
134 | |
120 | |
76 | |
65 | |
64 |