Forum Discussion

RobinH's avatar
RobinH
Helper I
6 years ago
Solved

Dynamic Age as Axis

Good afternoon   I'm trying this now for 3 days without a solution. Have found a few hints here in the community, but it just don't work. I would like to have a visual with the ages of collaborato...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, RobinH 

     

    Based on your description, you may create two calculated tables as follows.

    Age Table:

     

    Age Table = GENERATESERIES(0,300,1)

     

    DateSlicer:

     

    DateSlicer = CALENDAR(DATE(1950,1,1),DATE(2050,1,1))

     

     

    Then you can create a measure as below.

     

    Count = 
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            'Table',
            "DynamicAge", ROUND (
                IF (
                    ISBLANK ( 'Table'[Leaving Date] ),
                    DATEDIFF ( 'Table'[DoB], MIN ( 'DateSlicer'[Date] ), YEAR ),
                    DATEDIFF (
                        'Table'[DoB],
                        MIN ( 'Table'[Leaving Date], MIN ( 'DateSlicer'[Date] ) ),
                        YEAR
                    )
                ),
                0
            )
        ),
        [DynamicAge] IN FILTERS ( 'Age Table'[Value] )
    )
)

     

     

    You may restrict the range of year with the visual level filter.

     

    Result:

     

    Best Regards

    Allan

     

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

v-alq-msft's avatar
v-alq-msft
Community Support
6 years ago

Hi, RobinH 

 

Based on your description, you may create two calculated tables as follows.

Age Table:

 

Age Table = GENERATESERIES(0,300,1)

 

DateSlicer:

 

DateSlicer = CALENDAR(DATE(1950,1,1),DATE(2050,1,1))

 

 

Then you can create a measure as below.

 

Count = 
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            'Table',
            "DynamicAge", ROUND (
                IF (
                    ISBLANK ( 'Table'[Leaving Date] ),
                    DATEDIFF ( 'Table'[DoB], MIN ( 'DateSlicer'[Date] ), YEAR ),
                    DATEDIFF (
                        'Table'[DoB],
                        MIN ( 'Table'[Leaving Date], MIN ( 'DateSlicer'[Date] ) ),
                        YEAR
                    )
                ),
                0
            )
        ),
        [DynamicAge] IN FILTERS ( 'Age Table'[Value] )
    )
)

 

 

You may restrict the range of year with the visual level filter.

 

Result:

 

Best Regards

Allan

 

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

RobinH's avatar
RobinH
Helper I
6 years ago

Hi Allan v-alq-msft 

 

Thats awesome, thank you very much.

One more question, is there a possibility to create a tooltip that shows me the name, surname and DoB?

Because at the moment it shows me the list of all peolpe which fits in to the filters.

 

Thank you

Regards

Robin

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, RobinH 

     

    You can manage to achieve it. Please refer to the following link.

    https://docs.microsoft.com/en-us/power-bi/desktop-tooltips

     

    Here is my tooltip and result.

    tooltip:

     

    Result:

     

    Best Regards

    Allan

     

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

    • RobinH's avatar
      RobinH
      Helper I
      6 years ago

      Hi, v-alq-msft 

       

      Thank you for you speedy response.

      I already managed to get the tooltip showin me all names, doesn't matter if there is just one person with this age the tooltip keeps showing me the whole list, just filtered by title. As you can see in the Picture there are to much persons to see on one glance.

      I just would like to see the names from the bar i'm pointing on.

       

      Regards

      Robin

      • v-alq-msft's avatar
        v-alq-msft
        Community Support
        6 years ago

        Hi, RobinH 

         

        Please make sure that the slide for 'Keep all filters' is 'on' and try again. 

         

        While it is said in the official document as follows, it is normal that it will fail to pass the filters.

         

        For further information, you may refer to the following link.

        https://docs.microsoft.com/en-us/power-bi/desktop-drillthrough

         

        Best Regards

        Allan