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

[mpcarpenter]

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?

[amitchandak]

@mpcarpenter , 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

[harshnathani · Accepted solution]

Hi @mpcarpenter ,

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

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)