Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Grouping Age and Filter

Is there anyway to fix this? Or how to point where I did wrong?  Example : https://drive.google.com/file/d/1FJIKaGgkZrJHJsDoCvSMdL0YLe1-C5-N/view?usp=sharing
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

     

    Create a column.

    Column = DATEDIFF(Staff[Start Date],IF(ISBLANK(Staff[Resigned Date]),TODAY(),Staff[Resigned Date]),YEAR)

    Then create a measure.

    Measure =
IF (
    SELECTEDVALUE ( Range[max] ) = 1,
    COUNTROWS ( FILTER ( Staff, Staff[Column] < SELECTEDVALUE ( Range[max] ) ) ),
    COUNTROWS (
        FILTER (
            Staff,
            Staff[Column] >= SELECTEDVALUE ( Range[min] )
                && Staff[Column] <= SELECTEDVALUE ( Range[max] )
        )
    )
)

     The final result would be shown as below.

     

    Best Regards,

    Jay

    Community Support Team _ Jay Wang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

I check it. The range is not a common attribute. Also, the tables are not joined. I think just cross join is filtering. I am surprised even the first filter is work.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi! amitchandak  Okay... Is there anyway that we can find Age of Work at each year and do Age grouping. 

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Refer to attached file

    try like

    Current Age = CALCULATE(AVERAGEX(FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date]))),DATEDIFF(min(Employee[Start Date]),max('Date'[Date]),MONTH)),CROSSFILTER(Employee[Start Date],'Date'[Date],None))

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you for your answer amitchandak . How can I do Age grouping like this picture?

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Anonymous ,

         

        Create a column.

        Column = DATEDIFF(Staff[Start Date],IF(ISBLANK(Staff[Resigned Date]),TODAY(),Staff[Resigned Date]),YEAR)

        Then create a measure.

        Measure =
IF (
    SELECTEDVALUE ( Range[max] ) = 1,
    COUNTROWS ( FILTER ( Staff, Staff[Column] < SELECTEDVALUE ( Range[max] ) ) ),
    COUNTROWS (
        FILTER (
            Staff,
            Staff[Column] >= SELECTEDVALUE ( Range[min] )
                && Staff[Column] <= SELECTEDVALUE ( Range[max] )
        )
    )
)

         The final result would be shown as below.

         

        Best Regards,

        Jay

        Community Support Team _ Jay Wang

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.