Forum Discussion
DAX Progress calculation
- 6 years ago
Alright.
Thanks for your help.
Br
OK.
First, here's something about correct data structures in Power BI: Guy in The Cube. You can start with it. Briefly, a good model is one that is dimensional. A dimensional model is one that has dimensions and fact table(s) and these constitute what's called "the star schema." Fact tables are connected to dimensions via keys (99% integers) and NEVER directly to each other. Slicing is done ONLY through dimensions and all columns in fact tables must be hidden. Only measures are allowed to be visible if placed in a fact table. Here's an example of a star schema.
Yeah, I know you are surprised that this seemingly easy task requires some good and deep knowledge of DAX. This is because - as I said - of the model. If I wanted to get what you want and in an easy-to-code way, I'd do something different. I would create a dimension called Document that would store unique documents and their properties (something you could then slice by). Then I'd have a Dates dimension for time (which you do already have). Then I'd have a fact table that would store DocumentID, Date and Progress (this is because fact tables for the most part record PROCESSES over time). You don't have to store the proportion for each document in there because the proportion is an attribute of a document and does not change over time, hence it does not belong to the process. One more and very important thing. If you want to see progress through time as a non-stepped line, the best way is to store progress of each document through time in the fact table on the day granularity. So, each day for each document you should record the progress. If you do that, DAX will be simple and you'll get the "smooth" line that you've been dreaming of :)))
Alright.
Thanks for your help.
Br