Ways to make a legend dynamic by using DAX

[talitanieps]

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

[Daniel29195 · Accepted solution]

@talitanieps 

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! 🤠

[talitanieps]

Hello!

Thanks for your answer! 
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? 🙂

[Daniel29195 · Accepted solution]

@talitanieps 

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! 🤠

[talitanieps]

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!

[Daniel29195 · Accepted solution]

@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! 🤠

[talitanieps]

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!

[Daniel29195]

@talitanieps 

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,

[talitanieps]

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!

[talitanieps]

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!

[Daniel29195]

@talitanieps 

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! 🤠