Forum Discussion
Helper IIHow to create a segment with a measure depending on another segment?
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
Super UserAnthonyXelya -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"
)
Helper IIThat's what I did but I can't then put this mesure in a slicer 🙄
- rajendraongole1
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
Once again, you saved me! Thanks rajendraongole1 !! 🙂
- 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? 🙂