Forum Discussion

DianaT's avatar
DianaT
Helper I
6 years ago
Solved

Filter

Hi all,   I have a table with sales manager details recorded as below:   Abbey AbbeyHazel Peter PeterAbbey Hazel PeterHazel   Is there any way I can create a filter which will allow me to ...
Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    Please try to duplicate the original column and use 'Split Columns' function to split duplicated column in Query Editor:

    After Apply&Close, create a Slicer table:

    Table 2 = DISTINCT(UNION(SELECTCOLUMNS('Table',"A",'Table'[Sales Manage - Copy.1]),SELECTCOLUMNS('Table',"B",'Table'[Sales Manage - Copy.2])))

    Then create a check measure:

    Measure = IF(SELECTEDVALUE('Table 2'[A]) in FILTERS('Table'[Sales Manage - Copy.1])||SELECTEDVALUE('Table 2'[A]) in FILTERS('Table'[Sales Manage - Copy.2]),1,0)

    Apply this measure=1 to the visual, when you choose a name in slicer, the result shows:

    Here is my test pbix file:

    https://microsoftapc-my.sharepoint.com/:u:/g/personal/v-gizhi_microsoft_com/EaRtvIw6e5tMnwQaqwfraOIBaxH7z7CvTaFsqucUggp9jA?e=A9G0cP 

    Hope this helps.

     

    Best Regards,

    Giotto Zhi

v-gizhi-msft's avatar
v-gizhi-msft
Community Support
6 years ago

Hi,

 

Please try to duplicate the original column and use 'Split Columns' function to split duplicated column in Query Editor:

After Apply&Close, create a Slicer table:

Table 2 = DISTINCT(UNION(SELECTCOLUMNS('Table',"A",'Table'[Sales Manage - Copy.1]),SELECTCOLUMNS('Table',"B",'Table'[Sales Manage - Copy.2])))

Then create a check measure:

Measure = IF(SELECTEDVALUE('Table 2'[A]) in FILTERS('Table'[Sales Manage - Copy.1])||SELECTEDVALUE('Table 2'[A]) in FILTERS('Table'[Sales Manage - Copy.2]),1,0)

Apply this measure=1 to the visual, when you choose a name in slicer, the result shows:

Here is my test pbix file:

https://microsoftapc-my.sharepoint.com/:u:/g/personal/v-gizhi_microsoft_com/EaRtvIw6e5tMnwQaqwfraOIBaxH7z7CvTaFsqucUggp9jA?e=A9G0cP 

Hope this helps.

 

Best Regards,

Giotto Zhi

  • DianaT's avatar
    DianaT
    Helper I
    6 years ago

    Hi Giotto,

     

    I like this... Way leaner than the solution I have (which involves uploading seperate datalists for each manager...)

     

    My next obstacle though is that I'm trying to have my visual looks like this:

     

    But for this filter to work, I have to include manager info in my table:

     

    When I take away the Manager columns, the filter stops working and pulling in data from everyone...

     

     

    I would very much appreciate your insight on this.

     

    Many thanks,
    Diana