Lookup based on Dax Measure

[JEB85]

Hi,

Is it possible in powerpivot to lookup a value based on the direct result of a measure.

In the example below I have various people in column A and have created a dax measure to calculate the age of each person.

What I’d like to be able to do is, based on the person’s age, assign an age category to each as per the below:

Person	Avg. Age	Age Category
Person1	19.2	>10 and <=20
Person2	62.1	>60 and <=70
Person3	8.0	>0 and <= 10
Person4	29.7	>20 and <=30
Person5	71.1	>70 and <=80
Person6	38.3	>30 and <=40
Person7	9.0	>0 and <= 10
Person8	25.6	>20 and <=30
Person9	47.8	>40 and <=50
Person10	27.0	>20 and <=30

<colgroup><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>
</tbody>

Can this be done via another dax measure?

Thanks

 

[scottsen]

You probably want to avoid the worlds ugliest set of IF() statements, and have a table of AgeCategories. Then write a measures, something like:

=CALCULATE(VALUES(AgeCategory[Category]), FILTER(AgeCategory, AgeCategory[MinAge] > People[Age] && AgeCategory[MaxAge] <= People[Age]))

Or ... something like that

 

[JEB85]

Thanks for the reply! Will try it when I'm back in work and let you knows how it goes....

 

[JEB85]

Hi,

I now have my fact table with the fields as ‘Person’ and ‘Age’. I have created a measure to calculate Avg. Age of each person which is simply =CALCULATE(max(Ages[Avg Age])) – there is only one record per person so max is fine.

I also now have an age categories table set up as follows:

Min	Max	Age Category
0	50	> 0 and ≤ 50
50	100	> 50 and ≤ 100
100	200	> 100 and ≤ 200
200	300	> 200 and ≤ 300
300	400	> 300 and ≤ 400
400	500	> 400 and ≤ 500
500	600	> 500 and ≤ 600
600	700	> 600 and ≤ 700
700	800	> 700 and ≤ 800
800	900	> 800 and ≤ 900
900	1000	> 900 and ≤ 1000
1000	1E+14	> 1000

<tbody>
</tbody>

I have tried to replicate your formula but must have got it wrong somewhere as it’s delivering blanks. The formula I have to calculate the age category is:

=CALCULATE(VALUES(AgeCategories[Age Category]),filter(AgeCategories,AgeCategories[Min]>Ages[AvgAge]&&AgeCategories[Max]<=Ages[AvgAge]))


Which returns:

	Values
Person	AvgAge	AgeCategory
Person1	19.2
Person2	62.1
Person3	8
Person4	29.7
Person5	71.1
Person6	38.3
Person7	9
Person8	25.6
Person9	47.8
Person10	27
Grand Total	71.1

<tbody>
</tbody>

Any idea where I have gone wrong?

Thanks

 

[ImkeF]

If I may

You need to change the direction of your comparisons:


=CALCULATE(VALUES(AgeCategories[Age Category]),filter(AgeCategories,AgeCategories[Min]<Ages[AvgAge]&&AgeCategories[Max]>=Ages[AvgAge]))

 

[JEB85]

works great, thanks!!