Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
avatar456
Helper I
Helper I

Adding new columns (calculations) to existing matrix

Hello, this is what the data originally looked like:

 

NameTypeValue
Person 1Height187 cm
Person 1Weight76 kg
Person 2Height165 cm
Person 2Weight63 kg

 

I have changed these to the following by placing them in a matrix:

 

NameWeightHeight
Person 1187 cm76 kg
Person 2165 cm63 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?

 

NameHeightWeightBMI
Person 1187 cm76 kgX
Person 2165 cm63 kgY

 

Thanks for helping me out!

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@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.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
avatar456
Helper I
Helper I

@Greg_Deckler  

 

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! 

 

Greg_Deckler
Super User
Super User

@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.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler 

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_0-1664447607829.png

 

 

 

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_DecklerThanks, this was exactly what I was looking for!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.