Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rks
Resolver I
Resolver I

Filter Context in Nested Aggregations / CALCULATE, ADDCOLUMNS, SUMMARIZE

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:

ItemnoPartNoStockPerPartNoComment (FYI)
14775556516incomplete
14775556524incomplete
147495438218complete
147495438118complete
14749205912incomplete
14749205935incomplete
14749205952incomplete
14749205942incomplete
14749205923incomplete
14748625423complete
14748625413complete

 

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: 

ItemnoMinMax 
147755565218
147495438218
147492059218
147486254218

 

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

1 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

 

View solution in original post

5 REPLIES 5
CNENFRNL
Community Champion
Community Champion

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.Screenshot 2020-09-29 162354.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

@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
Community Champion
Community Champion

@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

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

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:

 

ItemnoMinMax
147755565218
147495438218
147492059218
147486254218
147482766218
147476714218

 

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

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors