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

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors