How to create multiple slicers for all charts in my reports such that they can also slice each other

[rumittal]

Hi All,

I am relatively new to PowerBI and I am trying to achieve below outcome, can you please help advice on the best way to achieve the same.

Goal : 

(1) For all the charts in my reports, there are some dimensions/columns that will slice/filter each of these charts -

JobOwnerTeam, ScenarioOwnerTeam, Bucket, Config.

(2) Also, these columns can also have many to many mapping among them , and should be able to slice each other. For example, if I take below table, for JobOwnerTeam - Team1 , I should get configs - ConfigX and ConfigY. For jobOwnerTeam - Team2, I should get ConfigY and ConfigZ.

Please refer below detailed info for my tables layout and methods I have tried but failed. 

And please help with advice on the same, it would be really helpful.

Sample Data Table:

Data table 1:

ResultValue	JobOwnerTeam	ScenarioOwnerTeam	Bucket	Config
OutcomeA	Team1	TeamP	B1	ConfigX
OutcomeB	Team1	TeamP	B1	ConfigY
OutcomeC	Team2	TeamQ	B2	ConfigY
OutcomeD	Team2	TeamQ	B2	ConfigZ

Data table 2 : 

Teams Score	JobOwnerTeam	ScenarioOwnerTeam	Bucket	Config
100	Team1	TeamP	B1	ConfigX
200	Team1	TeamP	B1	ConfigY
300	Team2	TeamQ	B2	ConfigY
400	Team2	TeamQ	B2	ConfigZ

Methods Tried:

Method1 :

a)creating 4 individual dimension table for each of JobOwnerTeam, ScenarioOwnerTeam, Bucket, Config and b)creating 4 slicers for each of them

--> It works to slice charts created on data tables but slicing across filters is not working.

Example, Selecting Team1 on JobOwnerTeam slicer is not selecting ConfigX and ConfigY on Config slicer.

Relationships : 

JobOwnerTeam - 1:many - Data Table1/2,

ScenarioOwnerTeam- 1:many - Data Table1/2,

Bucket- 1:many - Data Table1/2,

Config- 1:many - Data Table1/2,

Method 2 : 

a) In addition to 4 individual dimension table, creating one table that has combined unique combination values for all dimensions like: 

Dimension Table :

JobOwnerTeam	ScenarioOwnerTeam	Bucket	Config
Team1	TeamP	B1	ConfigX
Team1	TeamP	B1	ConfigY
Team2	TeamQ	B2	ConfigY
Team2	TeamQ	B2	ConfigZ

b) Trying to create bi-directional many to many relationship between each table lets say JobOwnerTeam and Dimension table , so selecting a JobOwnerTeam, will select ConfigX/Y --> It will slice further Config slicer (again connected to Dimension table by bi-directional many tomany relation).

Relationships : 

JobOwnerTeam - 1:many - Data Table1/2,

ScenarioOwnerTeam- 1:many - Data Table1/2,

Bucket- 1:many - Data Table1/2,

Config- 1:many - Data Table1/2,

JobOwnerTeam - many:many(bi-dir)- Dimension

ScenarioOwnerTeam- many:many(bi-dir) - Dimension

Bucket- many:many(bi-dir) - Dimension

Config- many:many(bi-dir) - Dimension

--> However, PowerBI doesnt allow for mulitple active many-many relationship between Data Table 1/2 and 4 slicer tables - JobOwnerTeam/ScenarioOwnerTeam etc. 

[Cmcmahan · Accepted solution]

I don't think you need those dimensions.  Your 2nd method using a combination table is the better way of doing this.  It could probably be named something better than combo table, like project table, or something relevant to what each valid set of job/scenario/bucket/config from the base tables should be called.

Here's my version: https://drive.google.com/file/d/1Xk_Vcsf791k4H03pZT3ojNi2znEL6Rqe/view?usp=sharing

I created the combo table, and then added an index as well.  If you've already got the table and can't add some sort of index, you can make a calculated column like this. Order doesn't matter, it just has to have some unique identifier per row.

CalcIndex = RANKX( 'Combo Table', [JobOwnerTeam]&[ScenarioOwnerTeam]&[Bucket]&[Config], [JobOwnerTeam]&[ScenarioOwnerTeam]&[Bucket]&[Config])

Then I normalized Data1 and Data2, by using the 'Combo Table'[Index] instead of each individual field, and related Data1/2 to the Combo Table.  I also made sure to set every base table's relationship to filter multidirectionally with the combo table.

Then I created 4 slicers, all of which can be based on either the original fact table's unique identifier, or on the value in the combo table, your choice.  There's also the bonus ability to filter other tables by other values like Score or ResultValue.

I think this meets all of your goals. I added an "Other Data" column to each of the base tables to show that you can associate other columns with each base table's unique identifier and use/display that as well. 

[rumittal]

@Cmcmahan, Thanks for such detailed help and explanation, and also sharing sample pbix file, this seems to be THE solution I was looking for.

I was trying to extract out the values of JobOwnerTeam, ScenarioOwnerTeam etc. in a separate combo table from Data Table 1/2.

I created an indexed column for combo table , however, how to map the values in DataTable 1/2 that have been extracted to combo table to be replaced by corresponding indexes ?

[rumittal]

@Cmcmahan Just specifying my exact confusion here. It would be great if you could help me with this info, it would complete the solution for me. From your response, I would like to understand how to achieve below: "Then I normalized Data1 and Data2, by using the 'Combo Table'[Index] instead of each individual field, and related Data1/2 to the Combo Table" Thanks in advance! 🙂

[Cmcmahan]

Sure thing. I've been busy for the last 2 days, so sorry for the long wait.

So database normalization is not necessary for your solution, it helps with managing your tables.  It all dances around the idea of normalizing your data, which there are debates about how much you really need to do in PowerBI.   I'm not sure exactly what your data is representing, but I'm sure there's a vocabulary term you would use to say "this specific combination of Job Owner/Scenario Owner/Bucket/Config is one ________" that's more useful than calling it a combo.  Just to pick a word and pull away from the technical meaning of combo/combination, I'm going to call each one a Setup.

The idea of using normalization techniques here is that you don't want to duplicate data within your model.  Originally, you were VERY often listing out the same information describing the Setup (Team, Scenario, Bucket, Config) each time you had a new row in Data1 or Data2.  This was 1) a lot of redundant data and 2) a nightmare to do filters for. If you wanted to specify that you wanted all results with the same Setup in an aggregation, you would have to use an expression like this

FILTER(
    'Data1', 
    'Data1'[JobOwnerTeam]=SELECTEDVALUE('Data1'[JobOwnerTeam]) && 
        'Data1'[ScenarioOwnerTeam]=SELECTEDVALUE('Data1'[ScenarioOwnerTeam]) && 
        'Data1'[Bucket]=SELECTEDVALUE('Data1'[Bucket]) && 
        'Data1'[Config]=SELECTEDVALUE('Data1'[Config])
)

And this is just for filter statements in DAX expressions. That you may need to use multiple times in a single expression. Gross. You had no easy method of specifying a relationship between the categories without listing out all 4 categories.  When I created the combo table, I was looking for a quick way to identify and then later relate all the Setups to each other. 

So when I created a table that listed all the available Setups, at first I just had a table that listed each Setup like this:

JobOwnerTeam	ScenarioOwnerTeam	Bucket	Config
Team1	TeamP	B1	ConfigX
Team1	TeamP	B1	ConfigY
Team2	TeamQ	B2	ConfigY
Team2	TeamQ	B2	ConfigZ

I needed a quick and easy way to define/reference each Setup, otherwise we run into the same issue of having to list out every category within a Setup, so I added an index.  Each Setup gets a unique id number, in this case, just starting at 1 and counting up.  Once you add that index to the table, you can replace the 4 columns of Setup information in Data1 and Data2 with a single value and relate it back to the Setup table.  Suddenly, that previous filter expression looks a LOT nicer:

FILTER( Data1, [SetupID] = SELECTEDVALUE([SetupID]) )

And once you relate the categories to the Setups and the Setups to the data, the model has a one way flow and looks like this:

Suddenly, you're able to slice on Bucket ID, and then the Setup Table gets filtered, and limits available options for the other slicers that are also slicing against the Setup Table, which makes them display in the way you want.  While this has added a new table, it's made management much easier.  The complexity of your data tables has dropped immensely:

 ->

So now I guess we get to the inevitable question: How do YOU accomplish this. When I did this example, I just manually created all 4 by hand. I'm assuming this is sample data, and that your real report has MANY more Setups. Assuming you can't just change the way the data is stored and imported, we can use the following expressions to create calculated tables and be sure we have all Setups. 

There are 2 ways to go about this.

The easy way is to just CROSSJOIN all teams to all scenarios to all buckets to all configs.  This is very easy and results in absolutely every possible combination, which may be useful, but may include many unused Setups. That would use this expression for the table

CrossjoinedSetups = 
CROSSJOIN(VALUES(JobOwnerTeam[Job Team Name]), VALUES(ScenarioOwnerTeam[Scenario Team Name]), VALUES(Bucket[BucketID]), VALUES(Config[ConfigName]))

And this expression as a calculated column for the index:

Index = RANKX( 'CrossjoinSetups', [Job Team Name]&[Scenario Team Name]&[BucketID]&[ConfigName], [Job Team Name]&[Scenario Team Name]&[BucketID]&[ConfigName])

Note that you may have to rename columns in your base category tables, since CROSSJOIN can't use columns with the same name.  The issue with this method is that it doesn't work very well with your 'slicers slicing slicers' requirement, since all combinations are in this table, so the slicer will still display all available values.  You could work around  and fix this, but that seems more difficult than I want it to be.

The harder, but more specfic way is to create a table only using Setups that exist in your original Data1 and Data2 tables.  If you have another table that could include different Setups, just add it into this.

Setups = DISTINCT(UNION(SUMMARIZE(OrigData1, [JobOwnerTeam], [ScenarioOwnerTeam], [Bucket], [Config]), SUMMARIZE(OrigData2, [JobOwnerTeam], [ScenarioOwnerTeam], [Bucket], [Config]))) 

And once again, add an index column:

Index = RANKX( 'Setups', [JobOwnerTeam]&[ScenarioOwnerTeam]&[Bucket]&[Config], [JobOwnerTeam]&[ScenarioOwnerTeam]&[Bucket]&[Config])

From there, replace category columns in Data1 and Data2 with the index, and you can go back to my original solution.

After going through the process of figuring out how to accomplish all this with DAX, I'm pretty sure it would have been MUCH easier to just start with Power Query.  I tried it, and was able to create a Setups table much faster and without any hassle:

let
    Source = Table.Combine({OriginalData1, OriginalData2}),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"JobOwnerTeam", "ScenarioOwnerTeam", "Bucket", "Config"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns", {"JobOwnerTeam", "ScenarioOwnerTeam", "Bucket", "Config"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Duplicates", "Index", 1, 1)
in
    #"Added Index"

This, I think, is the absolute best way to create this table.  You still have to go through and replace values in your Data1/Data2 tables with the appropriate unique index, but this gets you to that point the quickest and with the least hassle.

Hope this helped! I know that I learned something new.

[rumittal]

@CmcmahanSorry for sounding rushing, just wanted to make sure I am clarifying my questions accurately 🙂

Thanks for getting back quickly with such detailed information. I am in process of going through/trying out the method suggested by you. Will get back soon with results/more questions! 

[rumittal]

@Cmcmahan, Thanks for the awesome solution, it would really help me reduce the redundant data and for ease of filters.

However, As per your statement :

" You still have to go through and replace values in your Data1/Data2 tables with the appropriate unique index, but this gets you to that point the quickest and with the least hassle."

I tried using LOOKUPVALUE to replaces values in Data1/Data2 tables with unique index from the SETUP table (where indexed column was created to uniquely idenditfy each setup) . This creates an indirect relationship between Setup table and Data1/data2 table. 

however, due to this, I am not able to create Many-1 relationship between Data1/2 and Setup table , with bi-directional cardinality. It says that I am introducing circular dependency between both tables due to existing LOOKUPVALUE relationship.

Would you have more idea on this, how can I propagate index from Setup table -> Data 1/2 Table as well as have bi-directional Many-1 relationship ?

[Cmcmahan]

Sure. The trick here lies in creating the index without using LOOKUPVALUE or any other expressions that use the data from the original tables.  Once the index doesn't rely on any previous data, you should be able to create the relationships as you want. 

One way to accomplish this is to import the Data1/Data2 tables in power query again, and use those tables for creating the index.  Then you can set them to be ignored in your data model, and relate the Setup table to the original Data1/Data2 entries, since no circles can be created.