Forum Discussion

JOSERB's avatar
JOSERB
Frequent Visitor
6 years ago

INDEX MATCH in PBI / Merged Tables ?

Hello all,

 

I would like to thank you all for your help. I am a rookie in PBI and you are saving my life vey often (I would not have enough money to pay all the beers I should pay XD) .  Unfortunately I am blocked with the current situation:

 

I need to compare two different tables from two different merges (T1 and T2), and add a column in T1 (or a similar strategy, please do not hesitate if needed...).

This T1   will give values of the T2 if some match takes place. Easier with an Example:

 

1. I need to check if ID2 of T2 is present in the ID.1 of T1

 

2.  IF yes, I need to fill the T1 VALUE T1 with the value of T2. VALUE T2.  When the relationship is 1 to 1  there is no problem so marked as (OK) BUT.

 

3. IF several IDs are present in T2, I need to supply the one which correspond to the ID which date is bigger (see columns VALUE T1 and T2) marked as (!)

 

T1T1T1 T2T2T2 
ID.1DATE.1VALUE T1XID.2DATE.T2VALUE T2 
12017A (OK)X12018A 
22016B (OK)X22019B 
22017B (OK)X32019C 
32016C (!)X32020D 
32018C (!)X    
32020D (!)X    
42019null (!)X    

 

To solve it with Excel would be something like (using a matrix form):

 

VALUET1 = INDEX(VALUET2:VALUET2;MAX(MATCH(2;1/((ID.2:ID.2=ID.1.1)*(DATE.T2:DATE.T2<=DATE.T1)));1))

 

I have tried to merge both tables as well, but my PC struggles. When I do the data crossing the results are too big (obviously this is a simple case) an I can have 8 or 9 DATES for the same ID and thousands of IDs..

 

Any help please?

 

 

Need Help
Tips and Tricks

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    JOSERB , Create a new column in T1 like one of these

    countx(filter(T1, T1[ID1] =T2[ID2] && T1[DATE.1] =T2[DATE.2]),T2[VALUE])

    Or
    Minx(filter(T1, T1[ID1] =T2[ID2] && T1[DATE.1] =T2[DATE.2]),T2[VALUE])

     

    You can use for calculation

  • camargos88's avatar
    camargos88
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi JOSERB ,

     

    Try this calcultated column:

    Value =
    VAR _date = CALCULATE(MIN('Table (2)'[DATE]); FILTER('Table (2)'; 'Table (2)'[ID] = EARLIER('Table'[ID]) && 'Table (2)'[DATE] >= EARLIER('Table'[DATE])))
    RETURN CALCULATE(DISTINCT('Table (2)'[VALUE]); FILTER('Table (2)'; 'Table (2)'[ID] = EARLIER('Table'[ID]) && 'Table (2)'[DATE] = _date))
     
     

     

    • JOSERB's avatar
      JOSERB
      Frequent Visitor
      6 years ago

      it worked! thank you so much guys 🙂

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi JOSERB ,

     

    I modified camargos88 's expression. Please check:

     

    1. Create relationship between Table 1 and Table 2.

     

    2. Create a column like so:

    Column =
VAR _date =
    CALCULATE (
        MIN ( 'Table 2'[DATE.T2] ),
        FILTER (
            'Table 2',
            'Table 2'[ID.2] = EARLIER ( 'Table 1'[ID.1] )
                && 'Table 2'[DATE.T2] >= EARLIER ( 'Table 1'[DATE.1] )
        )
    )
VAR result =
    CALCULATE (
        DISTINCT ( 'Table 2'[VALUE T2] ),
        FILTER (
            'Table 2',
            'Table 2'[ID.2] = EARLIER ( 'Table 1'[ID.1] )
                && 'Table 2'[DATE.T2] = _date
        )
    )
VAR Count_ =
    COUNTROWS ( RELATEDTABLE ( 'Table 2' ) )
RETURN
    IF (
        Count_ = 1,
        result & " (OK)",
        IF ( Count_ > 1, result & " (!)", "null (!)" )
    )

     

    BTW, .pbix file attached.

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.