cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mabCOLONEL
Frequent Visitor

Index match custom column from seperate table

I have two tables.  Table A has a column to tag certain rows so I can filter them out of matrix results.  However, I already have a relationship built between the two tables.  So I want this column on Table A to filter Table B as needed.  I figured a new column in Table B can index match the value from Table A, and I can just add a slicer.  

Table A

Location IDCustomer IDStatus
1AOn
1BOn
1COff
2AOn
2BOff
2COn


Table B

ID (Location ID Related to Table A)Customer ID (can not relate to TableA)Status (Custom Column)ValueValue
1A=index(Status(match(Location ID, =ID, Customer ID, =Customer ID)Table BTable B
1BOnTable BTable B
1COffTable BTable B
2AOnTable BTable B
2BOffTable BTable B
2COnTable BTable B

 

So my end result would be the ability to filter my matrix from Table B by "Status" so the Values from Table B will only show based on my selection of "on" or "off".

I tried LookupValue but it doesnt seem to want to see my columns from Table A when writing the measure or new column formula.  
New Column = LookupValue('Table A'[Status],'Table A'[Location ID], [ID],'Table A'[Customer ID],[Customer ID])

I did try writing this as a measure as well and did not have luck getting the syntax correct for the identifiers.

1 ACCEPTED SOLUTION

Hi @rsbin ,
I ended up just merging from Table A into Table B.  I messed around until I figured out the need to 'expand' Table A once merged.  It was rather simple as I only really needed the one column.  

 

I have a feeling some of my tables migh have an issue as many of them will not 'stick' as values in a matrix and the majority of measures I attempt return invalid identifiers.  However, when I build the visuals from a seperate report and paste them in, they work fine. 

View solution in original post

2 REPLIES 2
rsbin
Super User
Super User

@mabCOLONEL ,

My test shows that the LOOKUPVALUE function should work:

LookupTest = LOOKUPVALUE( Lookup[Status], Lookup[Location ID], [ID], Lookup[Customer ID], [Customer ID]  )
rsbin_2-1669051508389.png

Please ensure your ID column is the same format in both tables - ie. either Text or Number.

If still not working, please show the error message your LookupValue is giving.

Regards,

Hi @rsbin ,
I ended up just merging from Table A into Table B.  I messed around until I figured out the need to 'expand' Table A once merged.  It was rather simple as I only really needed the one column.  

 

I have a feeling some of my tables migh have an issue as many of them will not 'stick' as values in a matrix and the majority of measures I attempt return invalid identifiers.  However, when I build the visuals from a seperate report and paste them in, they work fine. 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.