Filter Context in Nested Aggregations / CALCULATE, ADDCOLUMNS, SUMMARIZE

[rks]

Hi,

I have a table containing a Stock-Snapshot. I have certain item numbers, that consist of several package (imagine you buy a kitchen, that doesn't ship in one package, but comes in several packages).

I want to find those item numbers, that are incomplete - or to be precise count the number of itemnumbers that are incomplete. 

Here's my Dax-Code for now, which is wrong:

EVALUATE
VAR BaseTable =
    ADDCOLUMNS (
        SUMMARIZE (
            'Stock',
            'Stock'[Itemno],
            'Stock'[PartNo]
        ),
        "StockPerPartNo", CALCULATE ( [Sum Of Stock] ) // this gives me the packages per itemno and partno.
    )
VAR AggTable =
    ADDCOLUMNS (
        SUMMARIZE ( BaseTable, [Itemno] ),
        "Min", CALCULATE ( MINX ( BaseTable, [StockPerPartNo] ) ),
        "Max", MAXX ( BaseTable, [StockPerPartNo] )
    )
RETURN
    AggTable 

The result of BaseTable looks this. The comment is just to indicate, whether or not this itemno is actually incomplete or not. I have entered this manually:

Itemno	PartNo	StockPerPartNo	Comment (FYI)
147755565	1	6	incomplete
147755565	2	4	incomplete
147495438	2	18	complete
147495438	1	18	complete
147492059	1	2	incomplete
147492059	3	5	incomplete
147492059	5	2	incomplete
147492059	4	2	incomplete
147492059	2	3	incomplete
147486254	2	3	complete
147486254	1	3	complete

For instance, itemno 147755565 is incomplete because PartNo=1 is avaialable 6x and PartNo = 2 is only available 4 times.

So, essentially, when the minimum StockPerPartNo and the maximum StockPerPartNo per itemno are different, an item is incomplete.

However, the result of my AggTable shows this: 

Itemno	Min	Max
147755565	2	18
147495438	2	18
147492059	2	18
147486254	2	18

Obviously CT dosen't work in either way.... 

Eventually I would also have to check, whether per itemno min and max are different and only them count the result... 

But before, how would I need to alter my error-prone DAX statement in order to get - per itemno - the Min and Max - values from the "BaseTable"? From what I know, because of using an expression inside the "Base Table", data linage is lost and thus, the row context from the 2nd ADDCOLUMNS is rather uneffective.

Best regards,

Konstantin

[CNENFRNL]

Hi, @rks , from your description, I don't see the point creating an aggregation table instead of using a common table viz with straightforward, error-proof measures.

[rks]

@CNENFRNL Thank you for the insight. The final goal is to create a measure that counts the incomplete items in stock per day... Then I would like to drill into the itemno's or storage locations etc.. I thought to have first "declare" which itemnos are incomplete and then just count the rows of that anonymous table like SUMX(AggTable, if([Min]<>[Max], 1, 0)

[CNENFRNL]

@rks , then you may try this

EVALUATE
VAR BaseTable =
    ADDCOLUMNS (
        SUMMARIZE (
            'Stock',
            'Stock'[Itemno],
            'Stock'[PartNo]
        ),
        "StockPerPartNo", CALCULATE ( [Sum Of Stock] ) // this gives me the packages per itemno and partno.
    )
VAR AggTable =
    ADDCOLUMNS (
        SUMMARIZE ( BaseTable, [Itemno] ),
        "Min", CALCULATE ( MINX ( BaseTable, [StockPerPartNo] ) ),
        "Max", CALCULATE ( MAXX ( BaseTable, [StockPerPartNo] ) )
    )
RETURN
    AggTable

[rks]

Unforunately, becasue of using an expression in the BaseTable-variable:

 "StockPerPartNo", CALCULATE ( [Sum Of Stock] ) // this gives me the packages per itemno and partno.

data linage is lost. The result is the same Min/max value for each iteration:

Itemno	Min	Max
147755565	2	18
147495438	2	18
147492059	2	18
147486254	2	18
147482766	2	18
147476714	2	18

Maybe there's a completely different approach for solving these steps:

* Aggregate the granularity to itemNo / partNo

* Check the stock for each part no

* count rows for which the minimal stock per itemno over all partno's is different than the maximum stock per itemno over all partno's

[rks · Accepted solution]

The wrong calculation:

EVALUATE
VAR BaseTable =
    ADDCOLUMNS (
        SUMMARIZE (
            'Stock',
            'Stock'[Itemno],
            'Stock'[PartNo]
        ),
        "StockPerPartNo", CALCULATE ( [Sum Of Stock] ) // this gives me the packages per itemno and partno.
    )
VAR AggTable =
    ADDCOLUMNS (
        SUMMARIZE ( BaseTable, [Itemno] ),
        "Min", CALCULATE ( MINX ( BaseTable, [StockPerPartNo] ) ),
        "Max", CALCULATE ( MAXX ( BaseTable, [StockPerPartNo] ) )
    )
RETURN
    AggTable

The problem is the anonymous table that is not accissible because of the expression. Howeve,r there's another function that comes in handy: GROUPBY. CHeck out the documentation, it's quite cool in fact. 

The reqritten measure works:

EVALUATE
VAR BaseTable =
    ADDCOLUMNS (
        SUMMARIZE (
            'Stock',
            'Stock'[Itemno],
            'Stock'[PartNo]
        ),
        "StockPerPartNo", CALCULATE ( [Sum Of Stock] ) // this gives me the packages per itemno and partno.
    )
VAR AggTable =
    GROUP BY(
        BaseTable, 
        [Itemno] ,
        "Min", MINX ( CURRENTGROUP (), [StockPerPartNo] ) ,
        "Max", MAXX ( CURRENTGROUP (), [StockPerPartNo] ) 
    )
RETURN
    AggTable