Cumulative calculation in fact by utilizing the order column from a different table

[smpa01]

@AlexisOlson 

I am trying to undertsand if it is possible to bring the order column required for cumulative calcualtation from a seperate table and yet achieve the same cumulative result, had the order column existed in fact.

For example, let's suppose my data table is following

| fact.Cust | fact.OrderName | fact.Index |
|-----------|----------------|------------|
| Cust1     | Order1         | 1          |
| Cust1     | Order2         | 2          |
| Cust1     | Order3         | 3          |
| Cust2     | Order1         | 1          |
| Cust2     | Order3         | 3          |

I can write the following measure to achieve what I want where fact.Index being the order column

Measure = 
VAR _maxOrder =
    MAX ( 'fact'[fact.Index] )
VAR _mxCust =
    MAX ( 'fact'[fact.Cust] )
RETURN
    CONCATENATEX (
        FILTER (
            ALL ( 'fact' ),
            'fact'[fact.Index] <= _maxOrder
                && 'fact'[fact.Cust] = _mxCust
        ),
        'fact'[fact.OrderName],
        ",",
        'fact'[fact.Index]
    )

However, I was wondering if I don't have the fact.index column in fact table and insted I have order table with order.Index like this

| order.OrderName | order.Index |
|-----------------|-------------|
| Order1          | 1           |
| Order2          | 2           |
| Order3          | 3           |

Is it still possible to achieve a measure by utilizing the Order.Index in fact table to achieve what the following DAX query can

Table =
VAR _1 =
    ADDCOLUMNS (
        'fact',
        "_index",
            CALCULATE (
                MAX ( 'order'[order.Index] ),
                CROSSFILTER ( 'fact'[fact.OrderName], 'order'[order.OrderName], BOTH )
            )
    )
VAR _2 =
    ADDCOLUMNS (
        _1,
        "_concat",
            CONCATENATEX (
                FILTER (
                    _1,
                    [fact.Cust] = EARLIER ( [fact.Cust] )
                        && [_index] <= EARLIER ( [_index] )
                ),
                [fact.OrderName],
                ",",
                [_index]
            )
    )
RETURN
    _2

TLDR:- X Axis comes from fact, fact.Index does not exists, Cust.Index to utilize to achieve the accumulated result

pbix is attached

[AlexisOlson · Accepted solution]

It's certainly possible. Try something like this:

concat1 = 
VAR AllCustOrders =
    CALCULATETABLE (
        VALUES ( 'fact'[fact.OrderName] ),
        ALL ( 'fact'[fact.OrderName] )
    )
VAR MaxIndex =
    CALCULATE (
        MAX ( 'order'[order.Index] ),
        TREATAS ( VALUES ( 'fact'[fact.OrderName] ), 'order'[order.OrderName] )
    )
VAR Filtered =
    FILTER (
        ALL ( 'order' ),
        'order'[order.OrderName] IN AllCustOrders
            && 'order'[order.Index] <= MaxIndex
    )
RETURN
    CONCATENATEX ( Filtered, 'order'[order.OrderName], "," )

The better way to do it would be to use the 'order' table in the visual. If you do this, the code can be a bit cleaner. For example,

concat2 = 
VAR MaxIndex = MAX ( 'order'[order.Index] )
VAR Filtered =
    CALCULATETABLE (
        VALUES ( 'order'[order.OrderName] ),
        ALL ( 'order'[order.OrderName] ),
        'order'[order.Index] <= MaxIndex
    )
RETURN
    IF (  
        NOT ISEMPTY ( 'fact' ),
        CONCATENATEX ( Filtered, 'order'[order.OrderName], "," )
    )

Comparison:

[CNENFRNL · Accepted solution]

Simple filter propagations with expanded table,

[AlexisOlson · Accepted solution]

The best solution is to use dimension tables instead of trying to use your fact table as a dimension table. This will save you a ton of headaches in the long run.

If you create a 'cust' dimension table {"Cust1", "Cust2"} and set up the relationship, then all you need to write is this:

cumulative.Sale = 
VAR MaxOrder = MAX ( 'order'[order.Index] )
RETURN
    CALCULATE (
        SUM ( 'fact'[fact.Sale] ),
        REMOVEFILTERS ( 'order' ),
        'order'[order.Index] <= MaxOrder
    )

You can add additional filtering to hide the Cust2, Order2 row if you choose to.

[smpa01]

Sorry to bother you again @CNENFRNL  and @AlexisOlson. 

Thank you very much for these phenomal solutions and I learnt a lot.

However, I have a follow up question on the solution which I did not want to put in a seperate thread.  I would be happy to if you rather prefer me creating a new thread.

I have studied both your solutions and they look like this

In all cases, within the CONCATENATEX aggregator 'order' table was supplied and the aggreagtion happened on the rows from the same table 'order'[order.OrderName] because the scenario was such. But that was not actually my intention but I led you to believe that. Apologies for that.

My intention was  if the order column exists in 'order' table but the Row Context for the Aggregator function still exists in `'fact', can we still accumultae based on the expanded table?

Please allow me to demonstrate that with a better example.

If I have a single table like this and I want to calculate running total

| _testFact.Cust | _testFact.OrderName | _testFact.Sale | _testFact.Index |
|----------------|---------------------|----------------|-----------------|
| Cust1          | Order1              | 1000           | 1               |
| Cust1          | Order2              | 2000           | 2               |
| Cust1          | Order3              | 3000           | 3               |
| Cust2          | Order1              | 4000           | 4               |
| Cust2          | Order3              | 5000           | 5               |

I will do the following

But if I have two different tables like this

How do I still calculate the running total where I have the order column in 'order'  table and the Row context for Running Total still in 'fact'  table 

I am guessing the expanded table committed to memory looks like this. How to take advantage of this and still calculate the running total.

A Table  expression like following solves my problem. But how can I incorportae the same through a measure

Table =
VAR _1 =
    FILTER (
        CROSSJOIN ( 'fact', 'order' ),
        'fact'[fact.OrderName] = 'order'[order.OrderName]
    )
VAR _2 =
    ADDCOLUMNS (
        _1,
        "sum",
            SUMX (
                FILTER (
                    _1,
                    EARLIER ( 'order'[order.Index] ) >= 'order'[order.Index]
                        && 'fact'[fact.Cust] = EARLIER ( 'fact'[fact.Cust] )
                ),
                'fact'[fact.Sale]
            )
    )
RETURN
    _2

Pbix is attached and sorry for the long post.

[CNENFRNL · Accepted solution]

For being reluctant to spoil your tidy mosaic dashboard, I go on with my pbix file yesterday, with adding fact.sales columns to fact table.

I admit to be addicted to expanded table; so I tweak my DAX by keeping the essential part of expanded table method.

Bonus,

My fav, Excel formulas, our oldie but goodie do the trick here.

[smpa01]

@CNENFRNL  Thank you very much for your time. This is simply insane and definitely gives an idea as to how to utilize the expanded table. I will try to incorporate the expanded table concept for my solutions from now onwards just so I achieve fluency on using expanded table.

There is a possibility that you might get tagged on my post should I get stuck into utilizing this concept.

I simply can't comprehend for now how you could incorporate the expanded table concept in this. It is so difficult to visualize how to manipulate things from there. But now, I can see how can that be made possible. Like I said, this is insanely awesome!!!!

[CNENFRNL]

Aha, bravo, I finally lured another guy to cult ExpT. I'm poised to further discuss with you in this regard.

As to visualize it, it's not that difficult as expected based on my experience. Resort to whatever RDBMS you're comfortable with, Sql Svr, Oracle, DB2 etc., execute a sql with left joins, then gaze at the resulting dataset for 10 mins. Repeat this process 3 times a day! Within a week, the concept of expanded table is supposed to brand into your brain, I assure you 🤣

[AlexisOlson · Accepted solution]

The best solution is to use dimension tables instead of trying to use your fact table as a dimension table. This will save you a ton of headaches in the long run.

If you create a 'cust' dimension table {"Cust1", "Cust2"} and set up the relationship, then all you need to write is this:

cumulative.Sale = 
VAR MaxOrder = MAX ( 'order'[order.Index] )
RETURN
    CALCULATE (
        SUM ( 'fact'[fact.Sale] ),
        REMOVEFILTERS ( 'order' ),
        'order'[order.Index] <= MaxOrder
    )

You can add additional filtering to hide the Cust2, Order2 row if you choose to.

[smpa01]

Thanks @AlexisOlson 

[CNENFRNL · Accepted solution]

Simple filter propagations with expanded table,

[AlexisOlson]

This is very nice.

Do you know if the innermost 'fact' table calculation gets materialized in memory?

[CNENFRNL]

To my understanding, a full expanded table (e.g. 'fact' *:1 'order') is initialized in memory at the very beginning, being the most fundamental context for any DAX calculations.

No official supportive materials. Correct me pls if I get it wrong.

[smpa01]

@CNENFRNL  Thanks!!! awesome !!!

[AlexisOlson · Accepted solution]

It's certainly possible. Try something like this:

concat1 = 
VAR AllCustOrders =
    CALCULATETABLE (
        VALUES ( 'fact'[fact.OrderName] ),
        ALL ( 'fact'[fact.OrderName] )
    )
VAR MaxIndex =
    CALCULATE (
        MAX ( 'order'[order.Index] ),
        TREATAS ( VALUES ( 'fact'[fact.OrderName] ), 'order'[order.OrderName] )
    )
VAR Filtered =
    FILTER (
        ALL ( 'order' ),
        'order'[order.OrderName] IN AllCustOrders
            && 'order'[order.Index] <= MaxIndex
    )
RETURN
    CONCATENATEX ( Filtered, 'order'[order.OrderName], "," )

The better way to do it would be to use the 'order' table in the visual. If you do this, the code can be a bit cleaner. For example,

concat2 = 
VAR MaxIndex = MAX ( 'order'[order.Index] )
VAR Filtered =
    CALCULATETABLE (
        VALUES ( 'order'[order.OrderName] ),
        ALL ( 'order'[order.OrderName] ),
        'order'[order.Index] <= MaxIndex
    )
RETURN
    IF (  
        NOT ISEMPTY ( 'fact' ),
        CONCATENATEX ( Filtered, 'order'[order.OrderName], "," )
    )

Comparison:

[smpa01]

@AlexisOlson  thanks. This is awesome !!!