Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
In one table I have the Age of IDcontacts, and in another table I have AgeRanges such as :
Id | AgeFrom | AgeTo | Description |
1 | 0 | 10 | 0-10 |
2 | 11 | 20 | 11-20 |
3 | 21 | 30 | 21-30 |
4 | 31 | 40 | 31-40 |
5 | 41 | 50 | 41-50 |
6 | 51 | 60 | 51-60 |
7 | 61 | 70 | 61-70 |
8 | 71 | 80 | 71-80 |
9 | 81 | 90 | 81-90 |
10 | 91 | 200 | > 90 |
I would like to connect both tables. I would like to do the equivalent of a VLOOKUP TRUE in excel, such as 'if the value Age is between 0 and 10, then display'0-10'
I tried a column such as :
AgeID = IF([Age]<11,1,IF([Age]<21,2,IF([Age]<31,3,IF([Age]<41,4,IF([Age]<51,5,IF([Age]<61,6,IF([Age]<71,7,IF([Age]<81,8,IF([Age]<91,9,10)))))))))
so I could link it to the Age range table, but the rationale is wrong and I believe it's possible to do it with a Lookupvalue.
Any ideas?
Thanks in advance!
Solved! Go to Solution.
@Anonymous Please try this as a "New Column"
Assuming you age descriptions in a separate table and you want to retrieve the age description into another table based on the age.
AgeDesc = CALCULATE(VALUES(Test123Lkp[Description]),FILTER(Test123Lkp,Test123Data[Age]>=Test123Lkp[AgeFrom] && Test123Data[Age] <= Test123Lkp[AgeTo]))
Proud to be a PBI Community Champion
@Anonymous But if you see the screenshot that I've posted, it has age values like 25, 35 which are in between the age range but not as starting values. If you want to handle null values in age, then add another condition to exclude blank values.
AgeDesc = CALCULATE(VALUES(Test123Lkp[Description]),FILTER(Test123Lkp,Test123Data[Age]>=Test123Lkp[AgeFrom] && Test123Data[Age] <= Test123Lkp[AgeTo] && Test123Data[Age]<>BLANK()))
Proud to be a PBI Community Champion
Hi how to write that in Power Query M Language to create a new column please ?
@Anonymous Please try this as a "New Column"
Assuming you age descriptions in a separate table and you want to retrieve the age description into another table based on the age.
AgeDesc = CALCULATE(VALUES(Test123Lkp[Description]),FILTER(Test123Lkp,Test123Data[Age]>=Test123Lkp[AgeFrom] && Test123Data[Age] <= Test123Lkp[AgeTo]))
Proud to be a PBI Community Champion
@PattemManohar, actually it works only partially. With this formula it only notes as 11-20 those who's age is 11, 21-30 those who's age is 21, etc. Also, it notes 0-10 those who's age is Blank.
Any ideas?
@Anonymous But if you see the screenshot that I've posted, it has age values like 25, 35 which are in between the age range but not as starting values. If you want to handle null values in age, then add another condition to exclude blank values.
AgeDesc = CALCULATE(VALUES(Test123Lkp[Description]),FILTER(Test123Lkp,Test123Data[Age]>=Test123Lkp[AgeFrom] && Test123Data[Age] <= Test123Lkp[AgeTo] && Test123Data[Age]<>BLANK()))
Proud to be a PBI Community Champion
It works nicely, thanks a lot!
@Anonymous
For ranges you need to use the AND statement
e.g IF ( AND([Age]>=21, [Age]<30), 1, ....
I dont think your logic is correct
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
107 | |
70 | |
70 | |
43 |
User | Count |
---|---|
148 | |
104 | |
104 | |
89 | |
66 |