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" ) ))
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.
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.
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.
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?
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?
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.
Interesting point! I didn't think about overlap between the categories. You could get around this by creating a new unique column that includes the category and value, though it might start to get messy.
If you run this solution in an environment with periodic data refresh it's mandatory to create unique keys. Otherwise you will never forget the day when Mr. Green was added as a customer to your model that allows to show products by color and customers by name ;-)
@@BIElite I had this issue and yes, this works! I created the additional unique column that combined category+value column (seperated by a hyphen) and then set up the relationships using this unique column.
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!
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)
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)!
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?
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).
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?
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?
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.
Hey Rajat, yes if more values are added to the DIM tables, the calculated table will grow as well! Calculated tables are recalculated at the end of the data load process, so it will work just fine :)
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.
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.
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)
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?
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?
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
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 have the same issue, since my categories have the same value repeated under multiple fields, I cannot get a Many to One relationship. Any help around this issue would be appreciated. I will have to resort to bookmarks in the meantime!
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.
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
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.
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.
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
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.
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.
Haha see the other comment on using TREATAS instead of creating the inactive relationships. Then you're star schema only relies on the data source and not my silly hacks :)
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"
)
))
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.
Could you explain this in detail? I don’t know how to add a new row into the Dax table. What’s the value in the category column?
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!!
I like this one too! Thanks for watching
Just followed this step by step and it works perfectly, thank you for such an intuitive walkthrough! looks really slick and professional.
Awesome! one of the rare videos that address this challenge in Power BI
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.
As always , You share really great tricks and tips on Power BI !! You are real guru Parker!! Thank a ton again.
Haha thank you abhishek! I appreciate you watching.
Thank you. I struggled with dynamic legends and this really helped me!
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.
Just what I am looking for. Thank you very much!
No problem!
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?
Oh wow! This is such a great tip, I nearly massively overengineered a solution until I saw this!
How could we make the legend and x axis dynamic at the same time though?
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?
Thanks a lot. Really like the USERELATHIONSHIP approach.
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?
I wondered the same thing, and don't really have a strong opinion either way. Would love to hear your opinions on it.
I have issues with drillthrough using this method but do not with the index method.
Simply epic, liked and subscribed!
I have a question, do you think that this will work on huge data amount ?
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)
You could actually! Simply create inactive relationships on the same table multiple times, one for each column.
Hey thanks for the tutorial , now my question is that how can we apply drilldown in the visuals using same technique
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.
Interesting point! I didn't think about overlap between the categories. You could get around this by creating a new unique column that includes the category and value, though it might start to get messy.
If you run this solution in an environment with periodic data refresh it's mandatory to create unique keys. Otherwise you will never forget the day when Mr. Green was added as a customer to your model that allows to show products by color and customers by name ;-)
@@BIElite I had this issue and yes, this works! I created the additional unique column that combined category+value column (seperated by a hyphen)
and then set up the relationships using this unique column.
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!
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)
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)!
Thanks for sharing this
No problem at alL!
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?
This is a really great tip, thanks so much. It works like a charm.
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).
This is great! Can you synch the y axis dynamically for all 3 visuals?
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?
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?
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.
Hey Rajat, yes if more values are added to the DIM tables, the calculated table will grow as well! Calculated tables are recalculated at the end of the data load process, so it will work just fine :)
brilliant stuff mate!
thx for this. But how do drill-through with this?
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.
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.
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)
I got around it by amending as follows
Legend Catagories =
DISTINCT(
FILTER(
Union(
SELECTCOLUMNS(
'table',
"Catagory", 'table'[column1],
"Field", "label 1"
),
SELECTCOLUMNS(
'table',
"Catagory", 'table'[Supplier], "Field", "label2 "
)
)
,NOT(ISBLANK([Catagory])))
)
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?
Would there be a way to identify a hierarchy this way as well?
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?
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
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 have the same issue, since my categories have the same value repeated under multiple fields, I cannot get a Many to One relationship. Any help around this issue would be appreciated. I will have to resort to bookmarks in the meantime!
As always, it's awesome. loved it.
Glad to hear, Ramesh :)
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.
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
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.
That was so useful for me. Thanx
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.
I'm having a problem with circular dependency when I try to connect both tables. Did anyone get the same trouble?
Super Content As Always...Way to go
Thank you Rahul!
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
Hey, just a quick question, how to remove duplicated item in Category column? Thanks
Wrap the table in a DISTINCT() so LegendCategories = DISTINCT(UNION(...
@@mbelmer85 Thanks a bunch!
Lovely! Really neat. Thanks so much for sharing. Will definitely give this a go :-)
Glad to hear, Erica!
Amazing.. thank you👍
No problem, Aboli!
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.
can we control the colors ? please anyone ?
Brutal !!!
wow great amazing super
Thanks ashish!
this works only if you have a snowflake schema
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
Haha see the other comment on using TREATAS instead of creating the inactive relationships. Then you're star schema only relies on the data source and not my silly hacks :)
but you are stuck using TOTAL SALES measure!!! so it is only dynamic for ONE measure : ((((((((((((((((((((
You can always use a SWITCH measure or calculation groups. This video demonstrates dynamic categories, not measures.