Forum Discussion
Connect/filter table to two other tables
I have a feeling this will have something to do with USERELATIONSHIP, but am not great at DAX and not sure how to utilise it! I need to output a list of values (example: products) based on a country. The rest of the report views data from the product category tables, and I would like it to be linked so everything connects neatly in the report. Everything is DirectQuery and needs to be able to be updated in the web version to pull latest data. I can't attach the data due to privacy.
My four tables:
| Table A (Category1) | Table B (Country) | Table C (Category2) | Table D (product) |
| A_ID (product types - unique values) | B_ID (country code - filtered to have the single record "Germany") | C_ID (product types) | D_ID (unique values) |
| B_ID | B_ID | A_ID | |
C_ID |
Goal: Show list of products from Table D, based on the values of Table A and C. Records in Table D can have values in A_ID or C_ID or both. In report view, I want a table that shows values for D, and the connected values for A and C, and only for one country.
I want this as a view in my report, but only for categories that are connected to Germany.
| Product | Category1 | Category2 |
| X | A | 1 |
| Y | B | |
| Z | 2 |
What I tried:
This is the set-up I thought would work:
TableA TableD
A_ID 1:* A_ID
TableB TableC
B_ID 1:* B_ID
TableC TableD
C_ID 1:* C_ID
This only filters on TableA though, therefore not showing a product such as product Z. (Show items with no data is ticked).
I also tried to make a separate table that combined Category1 and Category2, which contained the values A_ID, B_ID, and C_ID - in this, values only exist for A_ID or C_ID, the rest are null-values. I also created a merged column based on this. But I'm not sure how I would connect it with TableD or if it would work (especially in cases where a record has values in both).
TableB TableA
B_ID 1:* B_ID
TableB TableC
B_ID 1:* B_ID
TableB newTable
B_ID 1:* B_ID
newTable TableD
mergedColumn (AC) 1:* ??
Is this at all possible, and if so, how? As I mentioned in the beginning, I tried to look at this post, but am not entirely sure how it would "transfer" in this case (if it's even applicable).