Comparing two tables that has blank values

[Anonymous]

Table1

Venue Code	Venue Name	Customer Code
Laguna	Suria	101Suria
Laguna	Suria	102Suria
Laguna	Suria	103Suria
Laguna	Suria	104Suria
Laguna	Suria	GENSURIA
Laguna	Suria	GENSURIA
Laguna	Suria	105Suria
Laguna	Suria	106Suria
Laguna	Suria	107Suria

Table2

Venue Code	Venue Name	Customer Code
Laguna	Suria	101Suria
Laguna	Suria	102Suria
Laguna	Suria	103Suria
Laguna	Suria	104Suria
Laguna	Suria	GENSURIA
Laguna	Suria	GENSURIA
Laguna	Suria
Laguna	Suria
Laguna	Suria

Filter = 
IF ( 
CONTAINS ( 
Table1, 
Table1[Venue Code], MAX ( Table2[Venue Code] ), 
Table1[Customer Code], MAX ( Table2[Customer Code] )
 ), 
1, 
0 
)

Above is the visual before I use the measure as a filter. So what I will do now is drag the measure into the filter pane and put it as "is 0" in order to show the venue that has a difference. In this case I am expecting Laguna to show up after I apply the filter since there is obviously a difference between the two tables.

But after I apply the filter it shows up as blank like this. 

I think it has something to do with the blank rows in Customer Code in Table2 because after putting in "null" instead of leaving those rows empty it actually showed up in the visual. Is there any way I can achieve the same thing while keeping those rows empty?

Any help would be greatly appreciated!

[edhans · Accepted solution]

I would take a different approach. Use the Model in Power BI for this. It returns this:

I set up a model using the Venue codes from both tables:

That lets me do this:

I have 3 measures:

Table 1 Records = COUNTROWs('table')

Table 2 Records = 
CALCULATE(
    COUNTROWS('Table 2'),
    NOT ISBLANK('Table 2'[Customer Code])
)

Difference = [Table 1 Records] - [Table 2 Records]

The difference measure goes in the filter pane and is not zero.

My file is here. Try to use Power BI the way it is intended - it expects a Star Schema, not a a SQL database where you are running queries on disconnected tables.

Microsoft Guidance on Importance of Star Schema

[Anonymous]

I'm still very very new to power bi so I guess I'm susceptible to making mistakes like these. But thank you so much for the very helpful and detailed response!

[edhans]

Glad I could help. That is why I posted the link to the importance of a Star Schema and the details. Keep studying, and post back to the forum with any questions. Hope the rest of your project goes well @Anonymous !

[Anonymous]

Hey there! Sorry to bother you again but I was trying to use your solution to achieve something else but I was having difficulties so I figured I should ask you again.

I added some new rows to the tables to make it a bit easier to understand. 

Table1

Venue Code	Venue Name	Customer Code
Laguna	Suria	101Suria
Laguna	Suria	102Suria
Laguna	Suria	103Suria
Laguna	Suria	104Suria
Laguna	Suria	GENSURIA
Laguna	Suria	GENSURIA
Laguna	Suria	105Suria
Laguna	Suria	106Suria
Laguna	Suria	107Suria
Bahamas	Sunway	Bah123
Bahamas	Sunway	Bah124
Bahamas	Sunway	Bah125

Table2

Venue Code	Venue Name	Customer Code
Laguna	Suria	101Suria
Laguna	Suria	102Suria
Laguna	Suria	103Suria
Laguna	Suria	104Suria
Laguna	Suria	GENSURIA
Laguna	Suria	GENSURIA
Laguna	Suria
Laguna	Suria
Laguna	Suria
Bahamas	Sunway	Bah123
Bahamas	Sunway	Bah124
Bahamas	Sunway	Bah125

I implemented a slicer with your solution which has three options "Difference", "Common", "All". So now I will have two card visuals which will display the count of customer code for each of the tables. If the slicer is selected at "All" then the card visual for table 1 will show 12 while the card visual for table 2 will show 9. If "Difference" is selected at the slicer then the card visual for table 1 should show 9 while card visual for table 2 will show 6. If "Common" is selected at the slicer then the card visual for table 1 should show only 3 while the card visual for table 2 will only show 3 as well.

I'm trying to figure out how to achieve that with a measure that I will put into the card visual. This is what I have so far but it is just not working.

Count = 
VAR CurrentStatus = SELECTEDVALUE('Show Options'[Option])
VAR Checker = [Difference] \\this is the measure you provided in your solution
RETURN
SWITCH( 
    TRUE(),
    CurrentStatus = "Difference" , COUNTROWS(FILTER(Table, Checker <> 0 )),
    CurrentStatus = "Common" , COUNTROWS(FILTER(Table, Checker == 0)),
    CurrentStatus = "All", COUNTROWS(Table)
)