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 ...
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

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft 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