Tabular Model – DAX needed for running sum over any dimension

Question

Hi all. I'm a DAX newbie and need some guidance. I'm building a tabular model that will be published to an on prem 2019 analysis server. Our users interact with the model via an excel pivot table, configuring the pivot in an ad hoc way to answer questions. I need to create a formula for running sum of distinct accounts over any configuration the user chooses. Along those lines, I have a formula to calculate distinct accounts.

DAX is

Nbr Distinct Accts:=DISTINCTCOUNT([Accounts])  

This works great as I can swap in any dimension and get the correct number of distinct accounts. The problem I’m having is creating a running sum of distinct accounts over ANY dimension the user configures the pivot.

Looking at some DAX samples, I created a running sum over specific dimensions. For example I can create a DAX calc that correctly gives me a running sum of distinct account over a dimension I call months outstanding. This works great as long as MonthsOutstanding is on the pivot.

DAX is

Cumulative Distinct Accounts MonthsOutstanding=  
CALCULATE (  
DISTINCTCOUNT ('FCT_Table'[Account]),  
FILTER (  
ALL( 'MonthsOutstanding'[MonthsOutstanding]),  
 ' MonthsOutstanding '[MonthsOutstanding]   <= MAX ( ' MonthsOutstanding '[MonthsOutstanding]  )  
)  
)  

I have a 2nd Cumulative Calculation similar to above that calculates the Cumulative Distinct Accounts over periods – basically YYYYMM format. DAX is

Cumulative Distinct Accts Activity Period :=  
CALCULATE (  
DISTINCTCOUNT ('FCT_Table'[Account]),  
FILTER (  
ALL( 'ActivityPeriod'[ActivityDate]),  
 'ActivityPeriod'[ActivityDate]   <= MAX ( 'ActivityPeriod'[ActivityDate]  )  
)  
)  

Both of these give me the right answer IF the correct dimension is on the pivot. Here is a screen shot of a pivot configured to the 2nd DAX calculation - Activity Period. The 3rd column (Cumulative Distinct Accts Act Prd) is correct. Since the MonthsOutstanding dimension is not on the pivot, it seems to default to Nbr Distinct Accounts. This reverses when I swap out the dimension. Is there a more generic way to write the DAX for the Cumulative Distinct Accounts where it could calculate it based on any values in the pivot? Thanks!

Answer 1

Here're two points why you really don't want to author a generic cumulative measure in DAX. First, your users are free to drop more than one attribute to rows/columns - what should the measure return for the various cells in such a pivot? Second, DAX doesn't have a way of querying which attributes are on axes - you'll need to manually code through all of them with IFs and ISINSCOPEs to hopefully find this out. Oh, and Excel will issue MDX queries to your model, and those tend to be convoluted...