Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello, this is what the data originally looked like:
Name | Type | Value |
Person 1 | Height | 187 cm |
Person 1 | Weight | 76 kg |
Person 2 | Height | 165 cm |
Person 2 | Weight | 63 kg |
I have changed these to the following by placing them in a matrix:
Name | Weight | Height |
Person 1 | 187 cm | 76 kg |
Person 2 | 165 cm | 63 kg |
What I am trying to do now is to add a new column to this matrix. I would like a column that calculates the BMI. But I cannot manage to call the weight and height columns in my formula, perhaps because they are not "real columns". Anyone have any idea how I can solve this?
Name | Height | Weight | BMI |
Person 1 | 187 cm | 76 kg | X |
Person 2 | 165 cm | 63 kg | Y |
Thanks for helping me out!
Solved! Go to Solution.
@avatar456 Try creating a measure like:
Measure =
VAR __Height = MAXX(FILTER('Table',[Type] = "Height"),[Value])
VAR __Weight = MAXX(FILTER('Table',[Type] = "Weight"),[Value])
RETURN
//BMI Calc goes here.
New rows have been added to my dataset, in addition to height and weight, there is now a type for residence. This creates a text in the 'Value' column. Because this column is now a mix of numbers and text, I can no longer filter my height, weight and BMI.
Can I specify in the formula whether it is text or numbers?
Cannot convert value " of type Text to type Number.
Any suggestions? Thanks voor helping me out!
@avatar456 Try creating a measure like:
Measure =
VAR __Height = MAXX(FILTER('Table',[Type] = "Height"),[Value])
VAR __Weight = MAXX(FILTER('Table',[Type] = "Weight"),[Value])
RETURN
//BMI Calc goes here.
Thanks, the calculation of my new column is now correct. But when I add it to my visual, the new column appears there multiple times (with each type). Any idea how I can solve this?
@avatar456 Right, so you have a few options. Option one is to use Name as Rows and create separate measures for Height, Weight and BMI like: Height Measure = MAX('Table'[Height]). Use these measures in your Values and don't use anything for Columns.
Another option is to turn off word wrap and shrink your unwanted columns.
Third option is: The New Hotness (Custom Matrix Hierarchy) - Microsoft Power BI Community
Fourth option is to switch to a Table visual instead of a Matrix.
I would vote for the 1st or 4th options personally.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |