Forum Discussion
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-table/m-p/1796985#M700960 |
Source table
| Address | Build Date | Year Build | Build Age Years(Calcualted column) | DAX expected result |
| 1 Dax Road | 01/01/2021 | 2021 | 0.4 | 0-10 Years |
| 2 Dax Road | 03/05/2009 | 2009 | 12.0 | 10-20 Years |
| 3 Dax Road | 31/05/2011 | 2011 | 10.0 | 10-20 Years |
| 4 Dax Road | 31/05/2011 | 2011 | 10.0 | 10-20 Years |
| 5 Dax Road | 08/09/1995 | 1995 | 25.7 | 20 - 30 Years |
| 6 Dax Road | 31/12/1991 | 1991 | 29.4 | 20 - 30 Years |
| 20 Power Street | 28/02/1985 | 1985 | 36.2 | 30 - 40 Years |
| 21 Power Street | 11/10/1970 | 1970 | 50.6 | 50 - 60 Years |
| 22 Power Street | 11/10/1971 | 1971 | 49.6 | 40 - 50 Years |
| 23 Power Street | 16/09/1972 | 1972 | 48.7 | 40 - 50 Years |
| 24 Power Street | 04/06/1950 | 1950 | 71.0 | 70 - 80 Years |
| 25 Power Street | 01/03/1900 | 1900 | 121.2 | 100 + Years |
Distribution table
| Order | Decade | Min | Max |
| 1 | 0-10 Years | 0 | 10 |
| 2 | 10-20 Years | 11 | 20 |
| 3 | 20 - 30 Years | 21 | 30 |
| 4 | 30 - 40 Years | 31 | 40 |
| 5 | 40 - 50 Years | 41 | 50 |
| 6 | 50 - 60 Years | 51 | 60 |
| 7 | 60 - 70 Years | 61 | 70 |
| 8 | 70 - 80 Years | 71 | 80 |
| 9 | 80 - 90 Years | 81 | 90 |
| 10 | 90 - 100 Years | 91 | 100 |
| 11 | 100 + Years | 101 | 1000 |
Hope this all makes sense.
Thank you RIchard
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
2 Replies
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
Great
Thank you for your super fast response. This functio works perfectly.
Richard
