Create multiple slicers from two separate tables

[Anonymous]

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

[_Su]

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. 

[v-alq-msft · Accepted solution]

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.

[Anonymous]

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.  

[amitchandak]

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

[edhans]

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