Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |