Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Hi Guys

I need to find out the following please; 

 

Reference Table;

Customer NumberMRDR number Price Valid from:Price Valid to: Promo NIV/Case  
111111234510/01/201915/02/20190.42
22222123467/07/201910/08/20190.82
333331234720/02/201919/03/20190.12

 

Table 2

  Customer  L8Pricing entry dateMaterial=mrdrSubtotal 4= NIV
1111121.03.2019123450.50
2222208.07.2019123460.80
3333320.04.2019123470.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
v-lid-msft
Community Support
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]
    )
)

 

6.jpg

 


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.

View solution in original post

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

View solution in original post

5 REPLIES 5
v-lid-msft
Community Support
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]
    )
)

 

6.jpg

 


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 

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

RobbeVL
Impactful Individual
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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