Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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.
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.
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:
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.
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.
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
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |