Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Comparing two tables that has blank values

Table1

Venue CodeVenue NameCustomer Code
LagunaSuria101Suria
LagunaSuria102Suria
LagunaSuria103Suria
LagunaSuria104Suria
LagunaSuriaGENSURIA
LagunaSuriaGENSURIA
LagunaSuria105Suria
LagunaSuria106Suria
LagunaSuria107Suria

 

Table2

Venue CodeVenue NameCustomer Code
LagunaSuria101Suria
LagunaSuria102Suria
LagunaSuria103Suria
LagunaSuria104Suria
LagunaSuriaGENSURIA
LagunaSuriaGENSURIA
LagunaSuria 
LagunaSuria 
LagunaSuria 

 

 

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!

Need Help
  • edhans's avatar
    edhans
    4 years ago

    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

     

4 Replies

  • edhans's avatar
    edhans
    Community Champion
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        edhans
        Community Champion
        4 years ago

        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 !