Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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.
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)
@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:
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
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
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |