Cross Reference tables

[Anonymous]

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 

and I created a formula thats giving me the following table

Targets Approached = If(SELECTEDVALUE('Form Answers'[If Yes, which Target?], 0) = SELECTEDVALUE('DIM - Entity'[Target],0), "Yes", "No")

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?

[AllisonKennedy · Accepted solution]

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.