Forum Discussion
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
- Anonymous2 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
- Tahreem24
Super User
Anonymous Try with Visual or Page level filter by filtering out the value less than 1.
- AnonymousNot 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
Super 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?
- AnonymousNot 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