Forum Discussion

kennwort's avatar
kennwort
Frequent Visitor
8 years ago
Solved

Slicer based on values from two columns

 

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.

Need Help
  • Omega's avatar
    Omega
    8 years ago

    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's avatar
    Zubair_Muhammad
    8 years ago

    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

7 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    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

    • kennwort's avatar
      kennwort
      Frequent Visitor
      8 years ago

      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's avatar
        Omega
        Impactful Individual
        8 years ago

        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 :D