Looking for a way to show values that appear in one table and not in another separate table.

[JordanJackson]

Hi there, 

Looking to get some help with a problem i've run into.

I have a table which contains all sites, I then have another table which contains any issues that have been raised by said sites. 

There is currently no direct relationship between the two tables and there are many duplicates of each project number. 

I'm looking for a way to show on a visual the sites that have not raised any issues yet. 

I will start by creating a table of distinct project numbers, but after this I am unsure of what the best method to do would be. 

What would be the DAX in order to create this?

Would doing this be possible just through filtering?

Any help would be greatly appreciated 

Thanks!

[Anonymous]

Hi @JordanJackson 

Will probably need more clarification as to what your data looks like but based on a bunch of assumptions I came up with this:

Data stored like:

where Records row can have duplicates of Site Numbers and Issue Numbers

--in your case not sure where the Project numbers come into it? Does each site have a corresponding project number?

Solution:

Use COUNTX to produce a measure that counts how many issues raised for each distinct Site Number. Can filter this measure for when count of issues is 0 to find which sites have no issues.

numIssuesPerSite = 

//counts how many rows in Records table per Site Number
//assuming each row in records corresponds to a single issue per site
var issuesPerSite = 
        COUNTX(
        'Records',
        RELATED('Sites'[Site Number])) //assuming values in 'Sites'[Site Number] distinct


//if Records contains no rows for a Site Number in Sites - return 0
return IF(ISBLANK(issuesPerSite), 0, issuesPerSite)

Demo:

Sample data:

 Output:

As can see in example: you can sort the number of issues on other columns eg. region.

To only have sites with no issues filter table by numissuesPerSite = 0

Hopefully this helps! Of course it depends how your data is stored but if its at all similar the same kind of approach using COUNTX should be what you are after.

Charlotte 🙂