Forum Discussion
Comparing two tables that has blank values
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
Community ChampionI 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
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 !
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) )