Slicer based on values from two columns

[kennwort]

I have a table that contains phone records. Each record has "Call From" and "Call To" columns with phone numbers in them. I would like to create a slicer that would allow the user to filter based on phone numbers from the "Call From" and/or "Call To" columns. For example, a record has Call From = 214-546-1802 and another record has Call To = 214-546-1802. When the user selects "214-546-1802" in the slicer, I would like to show both of these records. How do I go about doing that?

Appreciate any help I can get.

[Omega · Accepted solution]

Try the first part of the below solution: 

Combine Columns

Simply, you create a table with one column that contains all the numbers from both, Call From and Call To. Then, you drag that column to the slicer. 

Hope this helps. 

Thanks!

[v-jiascu-msft]

Hi @kennwort,

There could be a solution (or a workaround). You can try it in this file: https://1drv.ms/u/s!ArTqPk2pu-BkgT9WvhCrN_3FkaXW

1. Create an independent table of all phones.

AllPhones =
DISTINCT ( UNION ( VALUES ( Table1[CallFrom] ), VALUES ( Table1[CallTo] ) ) )

2. Create measure.

Measure =
VAR selectedCalls =
    VALUES ( 'AllPhones'[Calls] )
RETURN
    IF (
        ISFILTERED ( 'AllPhones'[Calls] ),
        IF (
            MIN ( 'Table1'[CallFrom] ) IN selectedCalls
                || MIN ( 'Table1'[CallTo] ) IN selectedCalls,
            1,
            0
        ),
        0
    )

3. Create a slicer of AllPhones[Calls]). You can keep the selected values only by using a visual level filter.

Best Regards!

Dale

[Omega]

Try using a hierarchy slicer: 

https://www.youtube.com/watch?v=DW5NZHTZlPw

[kennwort]

@Omega - Thank you for the suggestion. I looked at the hierarchy slicer but can't figure out how it fits this use case. The data I'm trying to filter isn't hierarchical. Both columns are phone numbers with no hierarchy between them. Can you help clarify?

[Omega]

If I understood correctly, each "Call From" have a list of "Call To", and you'd like to show both in slicer.

Using a hierarchy slicer, you can show all the "Call From" numbers as parents and underneath each "Call From" you will see the "Call To" numbers. 

I'm sorry if this is not what you are looking for 😄

[kennwort]

I see... its close, but not exactly what I need. I'm looking for a slicer that will filter the data if it is present in either of the two columns. There are many hundreds of thousands of phone numbers in the data, so I've enabled the "search" function in the slicer and what I'd like is to be able to search for a phone number in either the "Call From" or "Call To" fields and if it is found in either of them, then filter all such records. Eg: If I search for 214-546-1802 and it is found in some "Call From" records and some "Call To" records, then it those records should be shown.

I am currently doing this with two separate slicers, one for "Call From" and another for "Call To", but its not elegant.

[Omega · Accepted solution]

Try the first part of the below solution: 

Combine Columns

Simply, you create a table with one column that contains all the numbers from both, Call From and Call To. Then, you drag that column to the slicer. 

Hope this helps. 

Thanks!

[Zubair_Muhammad · Accepted solution]

@kennwort

You can unpivot the 2 columns(Call from and Call to) into a single column (lets say Call Type) as well using Query Editor

You can do it with DAX as well i.e. create a separate unpivoted table