Forum Discussion
Cross Reference tables
What volume of data are you working with here? You could try to merge the tables in the Power Query Editor, which will increase the total volume of data but acheive the results you're looking for:
The table above uses 'DIM - Entity'[Target], 'Form Answers'[Name] and 'Form Answers'[Approached Target?], and then applies the 'Show items with no data' to the 'DIM - Entity'[Target] field.
In your current dataset the 'Yes' show up fine, the 'unknown' show as blank, but the 'No' also show as blank. One way to fix this is Merge Queries;
In the Transform data Query Editor for the Form Answers table, click on the Home tab and select Merge Queries. Select the Form Answers and Cross Reference tables and choose the Form # column from each table. Choose Left Outer as the Join kind. Then click the double arrows on the new Cross Reference column and select 'Target' to expand the column. I renamed this 'Available Targets'.
Close and load the changes and your table should look like the photo above. You can then use DAX to replace the blanks with the word 'Unknown' if that's necessary.
If this solves your problem, please mark as solution. If you have questions about this solution, just let us know.
What volume of data are you working with here? You could try to merge the tables in the Power Query Editor, which will increase the total volume of data but acheive the results you're looking for:
The table above uses 'DIM - Entity'[Target], 'Form Answers'[Name] and 'Form Answers'[Approached Target?], and then applies the 'Show items with no data' to the 'DIM - Entity'[Target] field.
In your current dataset the 'Yes' show up fine, the 'unknown' show as blank, but the 'No' also show as blank. One way to fix this is Merge Queries;
In the Transform data Query Editor for the Form Answers table, click on the Home tab and select Merge Queries. Select the Form Answers and Cross Reference tables and choose the Form # column from each table. Choose Left Outer as the Join kind. Then click the double arrows on the new Cross Reference column and select 'Target' to expand the column. I renamed this 'Available Targets'.
Close and load the changes and your table should look like the photo above. You can then use DAX to replace the blanks with the word 'Unknown' if that's necessary.
If this solves your problem, please mark as solution. If you have questions about this solution, just let us know.