cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## Ways to make a legend dynamic by using DAX

Hello community members!

I need help to create a measure/way to make the legends for some visuals dynamic. And by dynamic I mean they need to follow a specific order and I can't think of a way of doing with without breaking my modelling.

So, I have several tables, and a dim table that connects with them, that has the following values:

221
222
231
232
241
242
This table, which I'll call campaign_dim, connects with other tables and filters their lines according to the value of campaign_dim.

What I need is that, when I select one of them on the filter, it shows the one I selected on the visual + 2 preceding it
for instance: 241 is selected, so on the visual, the legend should show 241, 232, 231;
if I selected 231, it should show 231, 222, 221.... so on, so forth.

I tried creating a bridge table with the corresponding values, but that didn't work either.

Any ideas on how to solve it? 🙂

Thanks in advance, folks

2 ACCEPTED SOLUTIONS
Super User
``````measure 9 =
var s = SELECTEDVALUE(data2[data])
VAR ds =

WINDOW(
-2, REL,
0,REL,
all(data2[data]),
ORDERBY(data2[data] , asc )
)
var res =
CALCULATE(
SUM(data[data]),
KEEPFILTERS(TREATAS(ds ,data[data]))
)

RETURN
if(
not ISBLANK(res), res , blank())

``````

modify it to this,,

and add it to the filter pane on visual .

as you see in the image above, when selecting 231, in the visual ,only 221,222,231 are showing  ( sum of data column in the visual is just a sum(col) )

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

Super User

@talitanieps
the measure works as follow :

first it takes the selectedvalue from the slicer

then the window function expand the selection to the selected value and the previous 2 ids @( which are ordered base on the ordeby paramter in the window function)

now you have a variable with the current selection and 2 previous campaign ids

now you need a way to propagate this table as a filter to the second table .

this is where treatas comes in handy.

And we use keepfilters to keep the filter context that we have from the visual (  table in the example )

So that only the 3 values listed in the variable ds would be displayed .

now the return is simply checking if the res isblank then return blank , else return the calculation

this if put in filter pane, wil filter the visual base on the 3 values that we had in the variable ds

In able to fully understand the measure, i would suggest to check the following functions :

window

treatas

keepfilters

assuming that you know how calculate works

hope this helps you .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution.

It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

9 REPLIES 9

Hello!

It partially worked... I did it all, but I couldn't understand where I should use the measure.....
The way I want it to work is that it changes the lines in a line chart....

So, when I change in my filter, I want it to reflect on the lines of the chart.... I tried putting the measure as legend, but it didn't work 😞
any ideass? 🙂

Super User
``````measure 9 =
var s = SELECTEDVALUE(data2[data])
VAR ds =

WINDOW(
-2, REL,
0,REL,
all(data2[data]),
ORDERBY(data2[data] , asc )
)
var res =
CALCULATE(
SUM(data[data]),
KEEPFILTERS(TREATAS(ds ,data[data]))
)

RETURN
if(
not ISBLANK(res), res , blank())

``````

modify it to this,,

and add it to the filter pane on visual .

as you see in the image above, when selecting 231, in the visual ,only 221,222,231 are showing  ( sum of data column in the visual is just a sum(col) )

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

Heyy, thank you so so so so much!! worked like magic! ❤️
If it's not asking too much, could you please explain the code of the measure?
Thanks once again!

Super User

@talitanieps
the measure works as follow :

first it takes the selectedvalue from the slicer

then the window function expand the selection to the selected value and the previous 2 ids @( which are ordered base on the ordeby paramter in the window function)

now you have a variable with the current selection and 2 previous campaign ids

now you need a way to propagate this table as a filter to the second table .

this is where treatas comes in handy.

And we use keepfilters to keep the filter context that we have from the visual (  table in the example )

So that only the 3 values listed in the variable ds would be displayed .

now the return is simply checking if the res isblank then return blank , else return the calculation

this if put in filter pane, wil filter the visual base on the 3 values that we had in the variable ds

In able to fully understand the measure, i would suggest to check the following functions :

window

treatas

keepfilters

assuming that you know how calculate works

hope this helps you .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution.

It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

Hello @Daniel29195

Thanks for your explanation! I'll study these functions for sure.
Now, I noticed that the duplicate dim introduced some issues with my modelling... I'm afraid that duplicating this dim won't do it.

Is there any other way, a different alternative, for me to acomplish the exact same result without duplicating my dim table?

Your help is very much appreciated, thank you!

Super User

the ducplicated dim , should not be linked to any table of your model.

it is used for this specific case .

if you didnt link it to other tables in the model ,then may i know what are the issues you are encountering ?

best regards,

Hmm.. I'll explain the best I can without giving away sensitive data... unable to reproduce this in a dummy, sorry bout that.

Dim 2 - no relationship with other tables
Original dim table - relationships with several other tables

So I have a page, with 6+ visuals, some visuals get data from a couple of different tables, but they're filtered by - now - this dim 2 table. When I filter, two of the visuals don't work, both are time related visuals; so, when I filter 241 for instance, it should return the months related to this 241 selection. This particular table is not being filtered by the Original Dim table, but through another table that has a relationship with Original dim table, direction set to both.
And I tried using the measure in this visual (which is a bar chart), but it didn't work either.

It looks more or less like this:

The calendar table also relates with this original dim table.
I'm trying to figure out which relationship is causing all of this fuss (probably the single 'direction both' existing on the model, but I'm not 100% convinced yet).

I hope that's clear.. let me know if you need any more details. Thanks once again!

Heyy @Daniel29195  it turns out I was being dumb and not using the legend column on those 2 visuals that weren't working.... so, my bad.

Thanks once again for this amazing solution!

Super User

output :

231 selected

232 selected

model : create a duplicated table from your orginal table with only the tbl_name[col_name]  which you want to filter on.

NB: the 2 tables shouldnt be linked .

the slicer should read from the newly created table .

the visual read from your normal dim table .

create the following measure :

``````measure 9 =
var s = SELECTEDVALUE(data2[data])
VAR ds =

WINDOW(
-2, REL,
0,REL,
all(data2[data]),
ORDERBY(data2[data] , asc )
)

RETURN
CALCULATE(
SUM(data[data]),
KEEPFILTERS(TREATAS(ds ,data[data]))
)

``````

the orderby in the window function is ordering your campaigns nb base on their nb ,

you can change the order base on the column you want .( so that i can take the previous 2 ) .

but in order to be able to orderby a different column , you need to add it in the all(  )  function ,

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors