Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Create multiple slicers from two separate tables

I have a two tables that are similar (most of the columns ina table A are in table B) 

, i have built dashboard where individual charts get columns  a particular table. Now i want to create multiple slicers that allows me to control all visualizations regardless of the table the charts were created from. I'm not sure how to do this the slicers seems not to be working for the visualization i created. Any luck? Thanks

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

     

    TableA:

     

    TableB:

     

    You may create three calculated tables and two measures as below.

    Calculated table:
Slicer Name = DISTINCT(UNION(DISTINCT(TableA[Name]),DISTINCT(TableB[Name])))
Slicer Class = DISTINCT(UNION(DISTINCT(TableA[Class]),DISTINCT(TableB[Class])))
Slicer Grade = DISTINCT(UNION(DISTINCT(TableA[Grade]),DISTINCT(TableB[Grade])))

Measure:
Visual ControlA = 
var _name = SELECTEDVALUE(TableA[Name])
var _class = SELECTEDVALUE(TableA[Class])
var _grade = SELECTEDVALUE(TableA[Grade])
return
IF(
    _name in DISTINCT('Slicer Name'[Name])&&
    _class in DISTINCT('Slicer Class'[Class])&&
    _grade in DISTINCT('Slicer Grade'[Grade]),
    1,0
)

Visual ControlB = 
var _name = SELECTEDVALUE(TableB[Name])
var _class = SELECTEDVALUE(TableB[Class])
var _grade = SELECTEDVALUE(TableB[Grade])
return
IF(
    _name in DISTINCT('Slicer Name'[Name])&&
    _class in DISTINCT('Slicer Class'[Class])&&
    _grade in DISTINCT('Slicer Grade'[Grade]),
    1,0
)

     

    Finally you need to put the 'Visual ControlA', 'Visual ControlB' in the corresponding visual level filter and use the 'Name', 'Class', 'Grade' column from 'Slicer Name', 'Slicer Class', 'Grade' table to filter the result.

     

     

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • edhans's avatar
    edhans
    Community Champion
    6 years ago

    You have a model problem. I suspect these tables need to either be merged into one, or you need some DIM tables around these FACT tables to relate them, then create the slicers from the DIM tables. You do not have a Star Schema, and that is what is giving you headaches. If you need help, you'll need do post some data.

    See Microsoft Guidance on Importance of Star Schema

     

    How to get good help fast. Help us help you.
    How to Get Your Question Answered Quickly
    How to provide sample data in the Power BI Forum

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , if there are too many common columns. You could have merged the tables.

    https://radacad.com/append-vs-merge-in-power-bi-and-power-query

     

    Another choice is to have common dimensions example

    City =DISTINCT(UNION(ALL(delivery[City Id]),all('order'[City Id])))

     

    Join with tables back and analyze.

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

    See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


    Appreciate your Kudos.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I agree with everyone you need some dimensional tables that will properly connect your 2 tables together then use your dimensional tables as your slicers and it should work jsut fine.  

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

     

    TableA:

     

    TableB:

     

    You may create three calculated tables and two measures as below.

    Calculated table:
Slicer Name = DISTINCT(UNION(DISTINCT(TableA[Name]),DISTINCT(TableB[Name])))
Slicer Class = DISTINCT(UNION(DISTINCT(TableA[Class]),DISTINCT(TableB[Class])))
Slicer Grade = DISTINCT(UNION(DISTINCT(TableA[Grade]),DISTINCT(TableB[Grade])))

Measure:
Visual ControlA = 
var _name = SELECTEDVALUE(TableA[Name])
var _class = SELECTEDVALUE(TableA[Class])
var _grade = SELECTEDVALUE(TableA[Grade])
return
IF(
    _name in DISTINCT('Slicer Name'[Name])&&
    _class in DISTINCT('Slicer Class'[Class])&&
    _grade in DISTINCT('Slicer Grade'[Grade]),
    1,0
)

Visual ControlB = 
var _name = SELECTEDVALUE(TableB[Name])
var _class = SELECTEDVALUE(TableB[Class])
var _grade = SELECTEDVALUE(TableB[Grade])
return
IF(
    _name in DISTINCT('Slicer Name'[Name])&&
    _class in DISTINCT('Slicer Class'[Class])&&
    _grade in DISTINCT('Slicer Grade'[Grade]),
    1,0
)

     

    Finally you need to put the 'Visual ControlA', 'Visual ControlB' in the corresponding visual level filter and use the 'Name', 'Class', 'Grade' column from 'Slicer Name', 'Slicer Class', 'Grade' table to filter the result.

     

     

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • _Su's avatar
    _Su
    New Member
    2 years ago

    My question was, suppose you have score columns in first table also and in both the table you have 'Year' column as well. Now you want to take any score column from any table and compare them based on different time basis. Like you will have two slicers to control different time period and one column chart suppose where taken two 'score' columns (sum of scores) will appear.