Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Filter in Visual - Column A and Column B Both = 0

Hi.  I'm really struggling with this one and have searched through several posts that on the face of it would appear to be the answer but I cannot make any of them work.

 

I have three tables:

Table A = Properties

Table B = Clients

Table C = Client Phones

 

Table A has a property phone number

Table B has a relationship to Table C which contains a phone number.

 

All 3 tables are linked in the data model and the rest of the visual contains various columns from related tables so the model seems to be working fine.

 

My Visual is a table and all I'm trying to do is export the resulting data to send to a team who will be contacting the clients.  But there is no point outputting a row if both the Property Phone Number and the Client Phone Number are invalid.  I'm in the UK and there shouldn't be a landline or mobile phone number that is less than 10 digits.

 

Here's a view of the visual:

I've created the two columns shown so that if length of phone number is less than 10 I return 0 otherwise 1.

 

The filter I need is to exclude rows where both Property_Phone_Length and Client_Phone_length are both 0.

 

Here's the code I've used to create those two columns:

 

 

Property_Phone_Length = IF(LEN('UMO300-Properties'[Property Phone Number])<10,0,1)
Client_Phone_Length = IF(LEN('UMO302-ClientPhones'[Client Phone Number])<10,0,1)

 

But all attempts to reference those 2 columns in a new column have failed.  Help please!!

 

Many thanks

David

 

 

 

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

     

    You could create a new column or a new measure and then filter on this column/measure. I’ve made a test for your reference:

    1\Create a new column

    OrFilter = If([Property_Phone_Length] =1 || [Client_Phone_Length]=1,1,0)

    2\Filter on ‘OrFilter’

     

     

    Best Regards,

    Bof

5 Replies

  • Anonymous Try with Visual or Page level filter by filtering out the value less than 1.

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      Tahreem24  thanks for the response.  The problem is that I'm trying to filter on both columns:

       

      Property_Phone_Length = 0 AND Client_Phone_length = 0.  The example you've shown is just on a single column.  I don't see any mechanism in Visual or Page filters that allows you to do that?

      • Tahreem24's avatar
        Tahreem24
        Icon for Super User rankSuper User

        Anonymous  I tried to create calculated columns for both Phone number length and applied easily on the filter pane. If Len is 10.


        I used the sample 4 rows of data and out of which only Label D row is having perfect 10 digits phone number in both the columns.

        Let me know if my understanding is correct or not?

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Anonymous ,

     

    You could create a new column or a new measure and then filter on this column/measure. I’ve made a test for your reference:

    1\Create a new column

    OrFilter = If([Property_Phone_Length] =1 || [Client_Phone_Length]=1,1,0)

    2\Filter on ‘OrFilter’

     

     

    Best Regards,

    Bof