TIP: If you don't have a table for each category you can use DISTINCT when you create the table: legend_category = DISTINCT( UNION( SELECTCOLUMNS( 'data_gfk', "Category", 'data_gfk'[PROCESSOR], "Field", "CPU" ), SELECTCOLUMNS( 'data_gfk', "Category", 'data_gfk'[GPU MODEL], "Field", "GPU" ), SELECTCOLUMNS( 'data_gfk', "Category", 'data_gfk'[Gaming PCs], "Field", "Product Line" ) ))

Awesome! one of the rare videos that address this challenge in Power BI

Just followed this step by step and it works perfectly, thank you for such an intuitive walkthrough! looks really slick and professional.

Greatly appreciate this trick. This is by far the easiest and powerful way to achieve this result I've seen so far. Thank you very much!!

Really easy and worked first time. I added this tweak for my own needs: If you're using this for legends and you want one of the field options to be 'No Legend' you can create a dummy row where the 'Category' is set to a literal instead of a column name and then just leave out the USERELATIONSHIP part of the CALCULATE when this field is selected in the slicer.

Thank you. I struggled with dynamic legends and this really helped me!

My first BI Elite video - the quality is incomparable. I really enjoyed the discussion on why we would use an inactive filter over an active one and could definitely use more on this topic including the implications of cardinality in an inactive setting.

Oh wow! This is such a great tip, I nearly massively overengineered a solution until I saw this!

As always , You share really great tricks and tips on Power BI !! You are real guru Parker!! Thank a ton again.

Can you explain why the many to one relationship with single cross filter direction is so important for this to work? When I initially tried this I didn't think to adjust the cardinality, assuming it would default to many to one, but it defaulted to One to One (which actually makes more sense to me). It wouldn't filter properly in that configuration.

How could we make the legend and x axis dynamic at the same time though?

I've been playing with this approach, and it works really well to control axis OR legend, but I can't figure out a way to control both (with two slicers obviously). Any ideas?

Simply epic, liked and subscribed! I have a question, do you think that this will work on huge data amount ?

Just what I am looking for. Thank you very much!

Very useful content and this is not the first time :) I really like the crystal clear way you are presenting. Keep up the good work Parker!

Great stuff. I guess this would not be possible on a single table. E.g. one table where you have country, state, city (no drill down)

Hey thanks for the tutorial , now my question is that how can we apply drilldown in the visuals using same technique

This is exactly what I've been looking for!!! However, this doesn't seem to play nice when other filters are placed on the page. Have you come across this issue? Any recommendations?

What do you think are the advantages/disadvantages of this method vs the one that does the annexes in the Power Query Editor, if any?

Thanks a lot. Really like the USERELATHIONSHIP approach.

Man they make it hard to do what should be a simple switch, "color by X" now "color by Y". Thanks of the post though, great solution and work around for missing functionality.

Hello! Fine trick, but what about non unique values in LegendCategories[Category]? Then you will have many-to-many relationship between LegendCategories and the dimension table.

Excellent trick, works great! Is it possible to "reset the colors" for each Field? Because he tries to use 1 per Category per Field and it makes a lot of options (and so weird colors)!

Does this only work if the categories are separated into different tables? I tried to do it with categories in the same table but I am unable to union them (step 1).

Great video! In my dashboard there is Row Level Security (RLS) used. As the measures in the video make use of USERELATIONSHIP() and this is - afaik - not compatible with RLS. My question is if there is solution for dynamic axes and legends in combination with RLS? Any hints are much apreciated!

Would it be easy to leave in a Total option? If a user wanted to toggle between a total and a categorical legend, could you give them the option within the measure?

Is there a way to give the user the ability to toggle on and off fields in the value section of a matrix? As in, there is by default, Sales, Cost, and GM but the user can toggle one or the other off? There would leave the matrix just showing cost per month on the chart instead of Sales, cost, and GM

Excellent video. Can this be done with two values X axis? I want to switch between a month + day view to a year + week without using bookmarks and 2 charts (and without concatenating the two values)

I've got a follow up question for you. I'm trying to use this in a matrix visual (it works amazing) and obviously I am using something for both the row and column values. Would it somehow be possible to use this kind of dynamic axis for both the columns and the rows? I am thinking the answer is no because the measure that you use is clearly defining to use a particular relationship but maybe there is some work around that I just don't know about.

Thanks for sharing this

This is a really great tip, thanks so much. It works like a charm.

I am not sure what to put for the expression in my case for CALCULATE( EXP, USERRELATIONSHIP(.... If I want to show all items in the given category that is selected, what do i put here?

I'm getting the following error when creating the measure "A function 'SELECTEDVALUE' has been used in a True/False expression that is used as a table filter expression. This is not allowed." , how would I fix this?

I'm using to to change the axis to daily, monthly, qtrly. How can I add a sort order to the Variable code, since each slicer selection would need to be sorted differently?

Very interesting! Thank you for sharing! I'm sure that end users will love it :) I didn't fully understand why do you have blank values for the "Legend Category" when creating a filter? Can you please elaborate a bit more on that? (min 5:10 of the video)

No idea why it can be one-to-many relationship between the Legend table and category tables. it can only allow me to select many to one relationship between them.

This is great! Can you synch the y axis dynamically for all 3 visuals?

Helo. I have seen and liked your solution. It is good, but I need a sollution that works fully like field parameters. That is I want to be able select (add and remove) any number of dimensions (multiple select) at the same time. Do you think that is possible? Regards

Would there be a way to identify a hierarchy this way as well?

That was so useful for me. Thanx

I tried to set this up using the filled map visual and it gave an error saying it couldn't determine the relationship. I tried setting the measure I set up with the calls to USERELATIONSHIP on the "tool tips" well but it made no difference when i had the relevant slicer entry selected. only when i forced the relationship to be enabled would it work. any idea on how to get this to work with the filled map?

Can we show variance in Percentage and amount based on selection. If yes then please guide right way. Because we can only show single formatting over bars i think so.

Is there a way to be able to do this with a live connection? We have most of if not all our reporting in BI using tabular models so wouldn't be able to create a custom table unless it was directly in the database.

For some reason Power BI didn't let me stablish the correct many-to-one relationship, which means it only allowed me to use the category tables to filter what in the video would be the "LegendCategories" table, it took me a while to figure out how to solve this and had to end up allowing bi-directional filtering (which I hate to do) to be able to do this

brilliant stuff mate!

thx for this. But how do drill-through with this?

As always, it's awesome. loved it.

Amazing work...really awesome ! will this work if new values are added in the source table for the 'CustomerCategoryName' or 'StateprovinceName' or 'ColorName' ? ASAIK calculated tables/columns take the values as such when they are created, they don't update after creation.

Having trouble with my relationship from LegendCategories table to other tables. I essentially have an Invoice Master table linked to a Office Master table based on sales office # (many to one). In my Office Master I have office # > office name > market > and region as fields. These are the fields I added as my categories. I am trying to link the LegendCategories table to the Office Master on the many to one relationship. For some reason when I attempt to add a relationship, many to one is not an option. I am trying to create a dynamic legend based on office, market, and region. Any guidance would be greatly appreciated!

I'm having a problem with circular dependency when I try to connect both tables. Did anyone get the same trouble?

I need to do something like this but with 3 columns in the same table, my table has 3 diferent status columns so i want to change the legend in the stacked bars chart using an slicer with the name of the 3 columns.

Lovely! Really neat. Thanks so much for sharing. Will definitely give this a go :-)

Hey, just a quick question, how to remove duplicated item in Category column? Thanks

Amazing.. thank you👍

can we control the colors ? please anyone ?

Super Content As Always...Way to go

Brutal !!!

wow great amazing super

this works only if you have a snowflake schema

but you are stuck using TOTAL SALES measure!!! so it is only dynamic for ONE measure : ((((((((((((((((((((

Great, but no easy to understand. For training purposes, it would useful to have a table data with only one or tow columns, and for example only 1 criteria column, and with a downloadable .pbix. Most of PowerBi videos are in fact almost useless because they are too complex to show the important point.

That is perfect star schema ;) Just kidding... Useful video though