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.

8 Replies

  • judspud's avatar
    judspud
    Icon for Solution Supplier rankSolution Supplier
    6 years ago

    Hi Anonymous 

     

    I'd guess that the filtering in you model can flow from the staff table to the age range table but not the other way.

     

    How are you wanting the visuals to interact?

     

    If you simply want them to not interact you can disable the interactions between them. Select a visual and turn on edit interaction under the Visual>Format tab. Select the block icon that appears on the other visual. Repeat the same process for the other visual.

     

    If you want them to both interact and highlight. Turn on cross filtering to both between the tables that contain the data.

     

    Hope this helps,

     

    Thanks,

    George 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi! judspud Thank you for your help. I want them both to interact and highlight. 
      Does cross filtering means create a relationship between table like below?

  • 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))