Forum Discussion
Calculations within a single fact column
Yes, it is possible to give you a formula but 1) it'll be extremely ugly, 2) it'll be extremely sub-optimal (meaning: slow) and 3) it'll be unnecessarily complex (which, again, means slow). What's more, the "fact table" is totally unsuitable for modeling in Power BI. It even goes against Best Practices of dimensional modeling. Are the ones who created the table even aware of their cardinal sins? I can't but wonder why people want to always sqeeze a square peg into a round hole and then they'll try hard to figure out why the model/system is slow and unusable. Do they really need to always learn the hard way? Really?
I'll tell you exactly why the solution with such a bad fact table will be totally flawed and unmaintainable. This is because you'll have to pair rows from the table for different measures (e.g., Price and Volume) and this will have to be done by joining on ALL the other columns. If you ever add more columns (attributes) to the model, you'll need to update the measures (possibly all of them!) and they will become slower and slower, precisely because the engine will have to work extremely hard to match rows from the same table and then do iteration over the matches executing operations like multiplication, division, addition.... over them. This will quickly become a nightmare for the developer to maintain and the user to use. I TELL YOU THAT TODAY. If you go down this route, you and the business will be sorry rather sooner than later. If I were you, I'd think twice before shooting myself in the foot.
- Guataha5 years agoFrequent Visitor
Thanks for the valuable feedback. The thing is that I did not designed the table, do not own the data and has anything to say about the database.
I only want to create a report based on this data. I will use your input for having an internal discussion on the data set-up, instead of trying to make this measure. Again thanks.
- Anonymous5 years agoNot applicable
Yeah. It's better to discuss the problem and find a good solution instead of having to constantly explain why things are not as smooth as they should be. PBI is great but only on condition that you get your data into the right shape. If you don't, prepare to suffer.