In this blog post, I aim to share my journey of learning how to create a dynamic DAX measure that effectively displays cumulative sums, while also resetting every zero occurs. I've discovered through community discussions on the Microsoft Fabric Community (community.fabric.microsoft.com) that there are certain challenging scenarios, particularly concerning cumulative values that reset at zero. The inspiration for this idea struck me while delving into the world of WINDOW DAX functions, specifically, the PARTITIONBY function within the WINDOW DAX function.
Link to learn WINDOW DAX function: https://learn.microsoft.com/en-us/dax/window-function-dax?wt.mc_id=DP-MVP-5004989
Link to learn PARTITIONBY DAX function: https://learn.microsoft.com/en-us/dax/partitionby-function-dax?wt.mc_id=DP-MVP-5004989
The PARTITIONBY DAX function, as its name implies, enables calculations to reset based on defined partitions. However, I didn't employ the actual PARTITIONBY DAX function in this case, I'll explain why in the later part of this article.
Picture this: You've got a straightforward data model in front of you. Your goal? Achieve a dynamic cumulative total visualization that resets at zero. Let's dive into how to make it happen!
Getting started is a breeze. The initial step involves crafting a table visualization just like the one depicted below and formulating a DAX measure – [Sum value measure:]
Sum value measure: =
SUM( Data[Value] )
Now, let's dive into the creation of a critical measure: [Cumulative Sum with Virtual Partition]. Here's the thought process behind crafting this measure:
Inside the virtual table of this measure, I'll use the VAR function to introduce a condition column that flags zero values on the date.
Within the same virtual table, I'll add a partition that assigns unique numbers whenever zero values occur. This is achieved by computing the cumulative sum of the condition column I created in the first step.
With this partition in place, I'll be able to identify the current row's partition number. Using this information, I can effectively separate the virtual table into distinct partitions.
Cumulate sum with creating virtual partition: = VAR _condition = //add or create condition column that shows zero when sum of value is zero. ADDCOLUMNS ( SUMMARIZE ( ALL ( Data ), 'Calendar'[Date] ), "@valuesum", [Sum value measure:], "@condition", INT ( [Sum value measure:] = 0 ) ) VAR _partition = //add or create partition column that generates different number every zero occurs. ADDCOLUMNS ( _condition, "@partition", SUMX ( FILTER ( _condition, 'Calendar'[Date] <= EARLIER ( 'Calendar'[Date] ) ), [@condition] ) ) VAR _currentrowpartition = //find current partition number MAXX ( FILTER ( _partition, 'Calendar'[Date] = MAX ( 'Calendar'[Date] ) ), [@partition] ) RETURN IF ( HASONEVALUE ( Data[Date] ), SUMX ( FILTER ( _partition, 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) && [@partition] = _currentrowpartition ), [@valuesum] ) )
For a clearer understanding of how the virtual table within the measure is constructed, I employed DAX Studio to visually inspect its structure. The resulting depiction is as follows:
As a result, the visualization looks like below.
Now, I couldn't resist trying out the WINDOW DAX function in combination with the PARTITIONBY DAX function in practice, instead of just keeping it as a concept. Here's what I did:
Yet, as the error message reveals, it turns out that the PARTITIONBY DAX function doesn't permit the use of a virtual column created within the same DAX measure.
I've explored numerous avenues to address the error message issue, but it continues to persist. If this feature isn't currently supported, I'm hopeful that a solution to support will be found soon. On the other hand, if there are valid reasons for this limitation, I'm eager to gain a deeper understanding.
I trust that this post has ignited your curiosity and made the journey of crafting cumulative sum DAX functions with reset conditions more enjoyable. Additionally, I encourage you to experiment with similar concepts, creating diverse types of partitions within virtually generated tables in your DAX measures, to solve a myriad of reset conditions. If you found this exploration fascinating, please consider sharing it with fellow data enthusiasts!
Comentários