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
JOSERB
Frequent Visitor

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?

 

 

4 REPLIES 4
Icey
Community Support
Community Support

Hi @JOSERB ,

 

I modified @camargos88 's expression. Please check:

 

1. Create relationship between Table 1 and Table 2.

relationship.PNG

 

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 (!)" )
    )

value.PNG

 

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.

camargos88
Community Champion
Community Champion

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))
 
Capture.PNG
 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



it worked! thank you so much guys 🙂

amitchandak
Super User
Super User

@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

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.