Forum Discussion

bturney's avatar
bturney
New Member
2 years ago
Solved

Cannot filter on "blank" rows.

I have a relationship between two tables... Table A is a list of Employees with their Employee ID and Table B is a list of Email accounts including their EmployeeID property.  Sample Data looks something like this:

 

Table A

First NameLast NameEmployee ID
JohnDoe1234
JaneDoe5678
BillyMadison8765
BennyGuzman4321

 

Table B

Email AddressEmpID
[email protected]1234
[email protected]5678
[email protected]4321
[email protected] 
[email protected] 
[email protected]9182

 

I have created a relationship between Table A and Table B that is a 1 to many relationship (note: Each EmployeeID in Table A has just 1 matching row in Table B, however Table B has rows that do not existing in Table A... including several rows where EmployeeID in Table B is blank)

 

Now I've combined these tables in a view and it looks something like this:

 

First NameLast NameEmployee IDEmail Address
JohnDoe1234[email protected]
JaneDoe5678[email protected]
BillyMadison8765 
BennyGuzman4321[email protected]

 

Which is great... but if I go to Filter this view on Email Address and tell it to just show where Email Address is blank... or is empty... it shows that there are no rows... however I remove the filter and I can clearly see rows where the email address is blank... 

 

Any thoughts on where this could be getting mized up?

Need Help
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    Hi bturney- We need to ensure that Power BI correctly identifies and filters rows with blank or empty email addresses

     

    I have created a calculated table as below:

    CombinedTable =
    ADDCOLUMNS(
        'TableA',
        "Email Address", LOOKUPVALUE('TableB'[Email Address], 'TableB'[EmpID], 'TableA'[Employee ID])
    )
     

     

    added one new column as above snapshot if you see IsEmailBlank

     

    IsEmailBlank = IF(
        OR(ISBLANK([Email Address]), [Email Address] = ""),
        "Blank",
        "Not Blank"
    )
     
     
    from report view, create a table chart, add the required fields into it. and apply visual level filter 
     

     

     

    you can see the blank record  . Hope it helps. please check.

     

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

1 Reply

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    2 years ago

    Hi bturney- We need to ensure that Power BI correctly identifies and filters rows with blank or empty email addresses

     

    I have created a calculated table as below:

    CombinedTable =
    ADDCOLUMNS(
        'TableA',
        "Email Address", LOOKUPVALUE('TableB'[Email Address], 'TableB'[EmpID], 'TableA'[Employee ID])
    )
     

     

    added one new column as above snapshot if you see IsEmailBlank

     

    IsEmailBlank = IF(
        OR(ISBLANK([Email Address]), [Email Address] = ""),
        "Blank",
        "Not Blank"
    )
     
     
    from report view, create a table chart, add the required fields into it. and apply visual level filter 
     

     

     

    you can see the blank record  . Hope it helps. please check.

     

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!