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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
cottrera
Post Prodigy
Post Prodigy

DAX - Calculating distributed age using a distribution table

Hi 

Has a property address table with 3 columns produced by source data. Column #4 is a calculated column that produces the age of the property in years (decimal)

 

I also have a second table that shows the distributed year (see below) I would like a DAX function that applies a result to Column #5 of my source table below based on the decimal age of the property (DAX Expected result)

 

The community have helped me with a similar request. But I could not use this function due to the Build Age Years(Calcualted column) not being recognised 

https://community.powerbi.com/t5/Desktop/DAX-Calculating-distributed-spend-using-a-distribution-tabl...

 

 

Source table

AddressBuild DateYear BuildBuild Age Years(Calcualted column)DAX expected result
1 Dax Road01/01/202120210.40-10 Years
2 Dax Road03/05/2009200912.010-20 Years
3 Dax Road31/05/2011201110.010-20 Years
4 Dax Road31/05/2011201110.010-20 Years
5 Dax Road08/09/1995199525.720 - 30 Years
6 Dax Road31/12/1991199129.420 - 30 Years
20 Power Street28/02/1985198536.230 - 40 Years
21 Power Street11/10/1970197050.650 - 60 Years
22 Power Street11/10/1971197149.640 - 50 Years
23 Power Street16/09/1972197248.740 - 50 Years
24 Power Street04/06/1950195071.070 - 80 Years
25 Power Street01/03/19001900121.2100 + Years

 

Distribution table

OrderDecadeMinMax
10-10 Years010
210-20 Years1120
320 - 30 Years2130
430 - 40 Years3140
540 - 50 Years4150
650 - 60 Years5160
760 - 70 Years6170
870 - 80 Years7180
980 - 90 Years8190
1090 - 100 Years91100
11100 + Years1011000

 

Hope this all makes sense.

 

Thank you RIchard

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@cottrera , you can create a new column like

 

Switch(True(),
[Build Age Years] >100,"100 + Years",
[Build Age Years] >90," 90 - 100 Years",
[Build Age Years] >80," 80 - 90 Years",
// add others
"0-10 Years" //default
)

 

add additional conditions as per need

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

2 REPLIES 2
cottrera
Post Prodigy
Post Prodigy

Great

 

Thank you for your super fast response. This functio  works perfectly.

 

Richard

amitchandak
Super User
Super User

@cottrera , you can create a new column like

 

Switch(True(),
[Build Age Years] >100,"100 + Years",
[Build Age Years] >90," 90 - 100 Years",
[Build Age Years] >80," 80 - 90 Years",
// add others
"0-10 Years" //default
)

 

add additional conditions as per need

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors