Find records that don't have a match in another table.

[ShawnHarrison]

Hi,

I am trying to find rows that don't have a match in another table. Basically, I have a main table in my data that contains records of a task. In another table, there is a row for each month of the year that contains a percent of how far along they have progressed. The data in that table would look something like this...

TaskID       Month              Percent

1                September       60

1                October            70

2                September        40

What I need is to be able to find a way to display in a table the ID's that don't have a row for a given month. For example, the user selects October in a slicer and only sees the task that doesn't have an entry for October, in this case, taskID 2.

[Ashish_Mathur]

Hi,

Don't you have a Year column in the first table?  If you do, it will become fairly easy to solve the question.

[v-tangjie-msft · Accepted solution]

Hi @ShawnHarrison ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create a measure. 

Missing Tasks = 
VAR SelectedMonth = SELECTEDVALUE('MonthTable'[Month])
VAR TasksWithMonth = CALCULATETABLE(VALUES(ProgressTable[TaskID]), ProgressTable[Month] = SelectedMonth)
RETURN
CONCATENATEX(
    EXCEPT(VALUES(MainTable[TaskID]), TasksWithMonth),
    MainTable[TaskID],
    ", "
)

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[ShawnHarrison]

Thanks! This looks like it will give me the results I need. I'm testing it out, but for some reason I keep getting an error "Function EXCEPT does not support joining a column of type Text with a column of type Integer. This seems to happen when I try to put the measure in a visual. Not sure what is causeing it. I am using the same data types that you have in your example solution. I notices however that your measure is in a text format but mine keeps defaulting to whole number without any way to change it. I'll keep playing with this to see if I can get it to work. 

[v-tangjie-msft]

Hi @ShawnHarrison ,

You can check if the TaskID field of the ProgressTable and MainTable is of numeric type.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.