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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Pros and cons of putting all values in one column (and using a dimension to distinguish fields)

I recently joined a team that uses a PBI model to hold what is, in effect, a data warehouse (lots of disparate data and indicators about a common set of organisations). One choice they’ve made, which seems to very odd to me, is to put all the numbers in one field (called “Value”) and distinguish them using a dimension (e.g. so “number of staff” will be in the same column as “number of calls”). They then use CALCULATE functions to pick out specific data items. This seems like a recipe for confusion and a pretty poor platform on which to do any decent analytics.

They’ve put a lot of work into their model, but it was one by someone with a background in databases rather than PBI.

I have 2 questions:

  • Am I missing something (is this actually a good way to proceed)?
  • How can I persuade them to rebuild things in a more sensible model?

 

1 ACCEPTED SOLUTION
harshnathani
Community Champion
Community Champion

Hi @Anonymous ,

 

Have a look at this blog.

 

Written by one of the BI Experts.

 

https://blog.crossjoin.co.uk/2020/08/09/the-pros-and-cons-of-modelling-measures-as-a-dimension-in-power-bi/

 

You can persuade you team after reading this.

 

Read the comments too. They are interesting

 

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Anonymous , I think this measure/column to dimension conversion is done to support a few format(complex table visual), which can be done better in this model.

 

The ideal is star schema. You can unpivot these columns

https://radacad.com/power-bi-basics-of-modeling-star-schema-and-how-to-build-it

https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

https://www.sqlbi.com/blog/marco/2017/10/02/why-data-modeling-is-important-in-powerbi/

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose: https://yodalearning.com/tutorials/power-query-helps-transposing-data/

 

refer these example for the format

https://community.powerbi.com/t5/Desktop/Matrix-Display-Values-above-Columns/td-p/256905
https://community.powerbi.com/t5/Desktop/grouping-measure-results/td-p/456795

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
harshnathani
Community Champion
Community Champion

Hi @Anonymous ,

 

Have a look at this blog.

 

Written by one of the BI Experts.

 

https://blog.crossjoin.co.uk/2020/08/09/the-pros-and-cons-of-modelling-measures-as-a-dimension-in-power-bi/

 

You can persuade you team after reading this.

 

Read the comments too. They are interesting

 

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.