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

[Anonymous]

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 · Accepted solution]

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

[Tahreem24]

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

[Anonymous]

@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]

@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]

Hi @Tahreem24 I really appreciate you taking the time to help me further.  I might not have fully explained what I'm trying to do.  It's actually an OR condition I'm trying to create.  In your sample data I would want to keep rows B, C and D because they have a valid value in EITHER Len1 OR Len2.

Using the filter in your example you're only showing rows where BOTH Len1 AND Len2 are 10.

Hope that makes sense and, more importantly, there is a way to create a filter that is an OR condition across 2 columns.