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,
I have to calculate the BMI (weight/height ^ 2)
This is in the table/query:
The BMI has to be calculated based on the last time an ID has answered the "What is your weight?" question.
In this example the weight to use for id001 is "78"
Tried a lot but I can't wrap my brain around it.
Can anyone help?
Thank you!
govi
Solved! Go to Solution.
@govi
One more approach if want to drop the measure against IDs level:
BMI-BY-IDS =
VAR _Height = CALCULATE(
MAX(BMI[Answer Text]),BMI[Question Text] = "What is your height?")
VAR _Weight = CALCULATE(
LASTNONBLANKVALUE(BMI[Date],MAX(BMI[Answer Text])),BMI[Question Text] = "What is your weight?")
RETURN
POWER(
DIVIDE(
_Weight,
_Height
),
2
)
This Measure will work at almost any level, you add this to the table you showed:
BMI Calculation =
VAR Q_H = "What is your height?"
VAR H =
CALCULATE(
MAX(BMI[Answer Text]),
FILTER(
ALLEXCEPT(BMI,BMI[ID]),
BMI[Date]=CALCULATE(MAX(BMI[Date]),ALLEXCEPT(BMI,BMI[ID]), BMI[Question Text]=Q_H)
),
BMI[Question Text]=Q_H
)
VAR Q_W = "What is your weight?"
VAR W =
CALCULATE(
MAX(BMI[Answer Text]),
FILTER(
ALLEXCEPT(BMI,BMI[ID]),
BMI[Date]=CALCULATE(MAX(BMI[Date]),ALLEXCEPT(BMI,BMI[ID]), BMI[Question Text]=Q_W)
),
BMI[Question Text]=Q_W
)
RETURN
// CONCATENATE(W1,H1)
POWER(
DIVIDE(
W,
H
),
2
)
If you are satisfied with my answer, please mark it as a solution so others can easily find it.
Don't forget to give KUDOS 👍 to replies that help answer your questions
Subscribe to ExcelFort: Learn Power BI, Power Query, and Excel
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi @govi ,
You may create measure like DAX below.
BMI =
VAR _MaxWeightDate =
CALCULATE (
MAX ( Table1[Date] ),
FILTER (
ALLEXCEPT ( Table1, Table1[ID] ),
Table1[AnswerText] = "What is your weight"
)
)
VAR _MaxHeightDate =
CALCULATE (
MAX ( Table1[Date] ),
FILTER (
ALLEXCEPT ( Table1, Table1[ID] ),
Table1[AnswerText] = "What is your height"
)
)
VAR _Weight =
CALCULATE (
MAX ( Table1[Date] ),
FILTER (
ALLEXCEPT ( Table1, Table1[ID] ),
Table1[AnswerText] = "What is your weight"
&& Table1[Date] = _MaxWeightDate
)
)
VAR _Height =
CALCULATE (
MAX ( Table1[Date] ),
FILTER (
ALLEXCEPT ( Table1, Table1[ID] ),
Table1[AnswerText] = "What is your height"
&& Table1[Date] = _MaxHeightDate
)
)
RETURN
POWER ( DIVIDE ( _Weight, _Height ), 2 )
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@govi
One more approach if want to drop the measure against IDs level:
BMI-BY-IDS =
VAR _Height = CALCULATE(
MAX(BMI[Answer Text]),BMI[Question Text] = "What is your height?")
VAR _Weight = CALCULATE(
LASTNONBLANKVALUE(BMI[Date],MAX(BMI[Answer Text])),BMI[Question Text] = "What is your weight?")
RETURN
POWER(
DIVIDE(
_Weight,
_Height
),
2
)
This Measure will work at almost any level, you add this to the table you showed:
BMI Calculation =
VAR Q_H = "What is your height?"
VAR H =
CALCULATE(
MAX(BMI[Answer Text]),
FILTER(
ALLEXCEPT(BMI,BMI[ID]),
BMI[Date]=CALCULATE(MAX(BMI[Date]),ALLEXCEPT(BMI,BMI[ID]), BMI[Question Text]=Q_H)
),
BMI[Question Text]=Q_H
)
VAR Q_W = "What is your weight?"
VAR W =
CALCULATE(
MAX(BMI[Answer Text]),
FILTER(
ALLEXCEPT(BMI,BMI[ID]),
BMI[Date]=CALCULATE(MAX(BMI[Date]),ALLEXCEPT(BMI,BMI[ID]), BMI[Question Text]=Q_W)
),
BMI[Question Text]=Q_W
)
RETURN
// CONCATENATE(W1,H1)
POWER(
DIVIDE(
W,
H
),
2
)
If you are satisfied with my answer, please mark it as a solution so others can easily find it.
Don't forget to give KUDOS 👍 to replies that help answer your questions
Subscribe to ExcelFort: Learn Power BI, Power Query, and Excel
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi @Fowmy @v-xicai @amitchandak
Thanks for you help!
I used @Fowmy 's solution. Will check @v-xicai 's later
Thanks again
govo
@govi , Something like this
sumx(summarize(Table, Table[ID] ,"_h", calculate(max(Table[AnswerText]),Table[questionText]="What is your Height")
,"_w", calculate(max(Table[AnswerText]),Table[questionText]="What is your Weight")
),power(divide([_w],[_h]),2))
Sumx can be replaced with averageX , power function also you need to check need
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |