Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RobinH
Helper I
Helper I

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 collaborators of a unit. The Age should be calculated by a year filter with the next 4 Years to chose from. Perhaps, but this would be super luxury, with a breakdown to month or quater.

I Was able to create a messure for the age by a given Date, but i can not use this messure as an axis.

In best case there is also a possibilty to hide people older than 65 (retirement age) and people who have left the unit.

 

Here you find a example of how it should look like (more or less)

 

Hope it's even possible.

Regards

Robin

 

 

 

 

1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

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.

d1.png

 

Result:

d2.png

 

Best Regards

Allan

 

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

View solution in original post

6 REPLIES 6
v-alq-msft
Community Support
Community Support

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.

d1.png

 

Result:

d2.png

 

Best Regards

Allan

 

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

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

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:

a1.png

 

Result:

a2.png

 

Best Regards

Allan

 

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

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

Hi, @RobinH 

 

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

a1.png

 

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

a2.png

 

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

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

 

Best Regards

Allan

 

Hi, @v-alq-msft 

 

Thank you very much, managed after read, try and error.

 

Best regards

Robin

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.