How to create a segment with a measure depending on another segment?

[AnthonyXelya]

Hello,

I have a list of societies with their creation date.

In a table I would like to show the list of societies that have less than 1 year, between 2 and 5 years, more than 5 years.

The difficulty here is that I would like to have these slices based on another segment where I choose a period.

That is, I would like to know the societies that had between 2 and 5 years in january 2022.

I have created a measure for the seniority  : 

DATEDIFF(SELECTEDVALUE('Entités'[Date_de_création]), MIN(Calendrier[Date]), YEAR) => not sure of the "SELECTEDVALUE"

And another measure for the slices, with a SWITCH, but I can't put that measure in a segment... 😞

Can someone help me please?

[_AAndrade]

Hi @AnthonyXelya,

Can you provide an example with some data so I can have a look?

[AnthonyXelya]

You only take a table with an ID and a random date column and it will do 🙂

[rajendraongole1]

Hi @AnthonyXelya -create a new calculated Column in the Societies Table

Society_Age_In_Years =
DATEDIFF('Entités'[Date_de_création], MAX(Calendrier[Date]), YEAR)

Now create a measure to Categorize Societies by Age Group

Society_Age_Category =
SWITCH(
TRUE(),
[Society_Age_In_Years] < 1, "Less than 1 year",
[Society_Age_In_Years] >= 2 && [Society_Age_In_Years] <= 5, "Between 2 and 5 years",
[Society_Age_In_Years] > 5, "More than 5 years",
"Unknown"
)

If the SELECTEDVALUE function is not behaving as expected, it might be due to the interaction between different filters and slicers. Ensure that the Calendrier[Date] slicer is not conflicting with other visuals or measures in your report

Hope this helps.

[AnthonyXelya]

The problem is, if I create a calculated column like you say, the result doesn't change when I change the date segment, so the switch will always return the same result.

And if I want to create it as a measure, I must put SELECTEDVALUE, or MIN before my 'Entités'[Date_de_création]

[rajendraongole1]

@AnthonyXelya -You're correct—using a calculated column won't dynamically respond to slicers because calculated columns are static once created. To solve your problem, you need to use a measure that dynamically calculates the age of each society based on the selected date range

create a measure as below:

Society_Age_In_Years =
DATEDIFF(
SELECTEDVALUE('Entités'[Date_de_création]),
MAX(Calendrier[Date]),
YEAR
)

Now use the switch for age category

Society_Age_Category =
SWITCH(
TRUE(),
[Society_Age_In_Years] < 1, "Less than 1 year",
[Society_Age_In_Years] >= 2 && [Society_Age_In_Years] <= 5, "Between 2 and 5 years",
[Society_Age_In_Years] > 5, "More than 5 years",
"Unknown"
)

[AnthonyXelya]

That's what I did but I can't then put this mesure in a slicer 🙄

[rajendraongole1 · Accepted solution]

Hi @AnthonyXelya - create a measure that dynamically calculates the age

Society_Age_In_Years =
VAR SelectedDate = MAX(Calendrier[Date])
RETURN
DATEDIFF(
SELECTEDVALUE('Entités'[Date_de_création]),
SelectedDate,
YEAR
)

this time, we’ll create a disconnected table that contains the age groups.This table is not connected to your main data model but will be used as a slicer

AgeGroups =
DATATABLE(
"AgeGroup", STRING,
{
{"Less than 1 year"},
{"Between 2 and 5 years"},
{"More than 5 years"}
}
)

create a measure that determines whether each society falls based on age group:

Selected_Age_Group =
VAR AgeInYears = [Society_Age_In_Years]
VAR SelectedGroup = SELECTEDVALUE(AgeGroups[AgeGroup])
RETURN
SWITCH(
TRUE(),
SelectedGroup = "Less than 1 year" && AgeInYears < 1, 1,
SelectedGroup = "Between 2 and 5 years" && AgeInYears >= 2 && AgeInYears <= 5, 1,
SelectedGroup = "More than 5 years" && AgeInYears > 5, 1,
0
)

In your table or matrix visual or choosen visual, add a filter where Selected_Age_Group equals 1. This will dynamically filter the societies based on the selected age group from the slicer.

Finally, add the AgeGroups[AgeGroup]  column to a slicer. When you select an age group in the slicer, your visual will dynamically show societies that fall within that age group based on the selected date.

hope this time it works. please check and confirm

[AnthonyXelya]

One last tip, please : when I use your solution, it works great as long as I choose only one option in the slicer... but if I want to show the societies who are older than 5 years and younger than one year, every data disapear 😞
How would you change your solution to accept more than 1 option? 🙂

[AnthonyXelya]

Once again, you saved me! Thanks @rajendraongole1 !! 🙂