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 ID | Customer ID | Status |
1 | A | On |
1 | B | On |
1 | C | Off |
2 | A | On |
2 | B | Off |
2 | C | On |
Table B
ID (Location ID Related to Table A) | Customer ID (can not relate to TableA) | Status (Custom Column) | Value | Value |
1 | A | =index(Status(match(Location ID, =ID, Customer ID, =Customer ID) | Table B | Table B |
1 | B | On | Table B | Table B |
1 | C | Off | Table B | Table B |
2 | A | On | Table B | Table B |
2 | B | Off | Table B | Table B |
2 | C | On | Table B | Table 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.
Solved! Go to 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.
My test shows that the LOOKUPVALUE function should work:
LookupTest = LOOKUPVALUE( Lookup[Status], Lookup[Location ID], [ID], Lookup[Customer ID], [Customer ID] )
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.
Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.
Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!
User | Count |
---|---|
215 | |
53 | |
46 | |
42 | |
41 |
User | Count |
---|---|
278 | |
209 | |
74 | |
73 | |
65 |