Forum Discussion
Cross Reference tables
I have two tables that I am importing into a dashboard. The first is a table created by Microsoft Forms answers. The second table is a cross reference table that shows which targets were asked about on which Form #. I am trying to build a dashboard that will show who has or has not approached which targets but also show when its unknown.
So for John, he approached no targets on Form #1 (Best Buy, JC Penny, Sears, Walmart), only approached Ebay on Form #2, and has not answered Form #'s 3 or 4. I have relationships that look like this
So I can see John aproached Ebay and didn't approach the customers on Form 1 or 2. But its also showing he didnt approach customers on Form 3 or 4 which he has not submitted answers for. Any idea how to make those customers show a null or unknown text since he hasnt filled out that form yet?
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.
1 Reply
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.