Forum Discussion

AnthonyXelya's avatar
AnthonyXelya
Icon for Helper II rankHelper II
2 years ago
Solved

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

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 diffi...
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    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

     

rajendraongole1's avatar
rajendraongole1
Icon for Super User rankSuper User
2 years ago

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's avatar
AnthonyXelya
Icon for Helper II rankHelper II
2 years ago

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's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      AnthonyXelya
      Icon for Helper II rankHelper II
      2 years ago

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

      • rajendraongole1's avatar
        rajendraongole1
        Icon for Super User rankSuper User
        2 years ago

        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