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
JanBuzek
Frequent Visitor

Employee age structure

 Hello,

 

I am trying to create a model that would predict future age structure of employees. To do this I wanted to have a line chart with Count of employees on Y-axis and different ages on X-axis. Then I would like to use a slicer to "move" the line to the right into the future as the employees would age.

Výstřižek.PNG

I have a table with employees and their age as a data source.

 

I wanted to create a what-if parameter and use this parameter in a custom column calculation but I found that this is not possible as columns are calculated on data load and not continuously according to user interaction.

 

 

Is there another way to accomplish what I am trying to do?

 

Thank you so much for any help.

 

Jan

 

1 ACCEPTED SOLUTION
v-jiascu-msft
Employee
Employee

Hi Jan,

 

Please download the solution from the attachment. 

1. Create an independent table [Ages] in case that no all the ages are listed. Don't establish a relationship.

Ages = GENERATESERIES(20, 65, 1)

2. Create a what-if parameter from 0 to the number you need, which means the years. For example, 1 year later.

Measure =
SUMX ( 'Table1', IF ( [Age] = MIN ( Ages[Value] ) - [Parameter Value], 1, 0 ) )

3. Create a visual. 

Employee-age-structure

 

Best Regards,
Dale

Community Support Team _ Dale
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

4 REPLIES 4
v-jiascu-msft
Employee
Employee

Hi Jan,

 

Please download the solution from the attachment. 

1. Create an independent table [Ages] in case that no all the ages are listed. Don't establish a relationship.

Ages = GENERATESERIES(20, 65, 1)

2. Create a what-if parameter from 0 to the number you need, which means the years. For example, 1 year later.

Measure =
SUMX ( 'Table1', IF ( [Age] = MIN ( Ages[Value] ) - [Parameter Value], 1, 0 ) )

3. Create a visual. 

Employee-age-structure

 

Best Regards,
Dale

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

Thank you, that's it!

 

Could you please explain to me how the measure actually works? I cannot seem to wrap my head around it. Specifically the condition:

[Age] = min(Ages[Value]) - [Parameter Value]

 

Why are you searching for the minimum Ages Value and then subtracting the parameter?

 

Thank you. I might be too dumb.

 

Jan

Hi Jan,

 

That's a challenge for me, too. The dates and time are dynamic.

1. The min() function aims to match the ages in the X-axis. Because there isn't a relationship. Actually, it returns a single value. Let's have a look at the example in the last snapshot, it's 27 in the lower visual (the red rectangle). 

2.  The [Parameter Value] is 0 at first. That means we don't add any years. It's simple in this scenario. When the age equals the X-axis value, we tag it 1. Or it's 0. Finally, we sum them up.

3. When we add some years to see the forecast, the X-axis is the ages in the future. So we need to delete the [Parameter Value] to match the actual age in the data table. For example, how many people will be 27 in one year later? We need to count the people who are 26 now. So it's 27 in the X-axis while it's 26 in the data table (the source).

 

Best Regards,
Dale

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

Thank you so much. I think I get it now:)

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.