topic Re: DAX to count order numbers in a composite model in DAX Commands and Tips

DAX to count order numbers in a composite model

Syk — Tue, 17 May 2022 15:59:28 GMT

Hello,

This problem is a little tricky..
I have 3 tables that need to be factored in here.

Sales order
Sales order profile
Order side custom Data

The problem is for each Online Req (in Order side custom data), there will be multiple sales orders with specific types.

I need to find the sales order number (in Sales order) with type "RO" (type in sales order profile) and subtract it by the other sales order number associated with the online req and count the reqs where that difference is <50. The idea being - if the RO sales order and the other sales order are close enough that would mean it was filled automatically.

Example:
Online req 1234 has 2 sales orders:

Sales order #567 of type "RO"
Sales order #579 of type "AP"
579 - 567 = 12 (subtract the actual order numbers from one another)
We would want to count this order as an auto fill

To top it off, this is a composite model connected to azure analysis services so the data model can't be modified. Any help on this is greatly appreciated!

Re: DAX to count order numbers in a composite model

johnt75 — Tue, 17 May 2022 15:20:38 GMT

You could try something like

Num auto filled = SUMX ( 'Order side custom data', VAR typeRO = CALCULATE ( SUMX ( RELATEDTABLE ( 'Sales Order' ), 'Sales Order'[quantity] ), 'Sales order profile'[type] = "RO" ) VAR typeAP = CALCULATE ( SUMX ( RELATEDTABLE ( 'Sales Order' ), 'Sales Order'[quantity] ), 'Sales order profile'[type] = "AP" ) VAR diff = typeAP - typeRO RETURN IF ( diff < 50, 1 ) )

Re: DAX to count order numbers in a composite model

Syk — Tue, 17 May 2022 15:58:47 GMT

This looks like its summing up all of the orders with an RO type and again with AP type then subtracting. Essentially 5000 orders have AP type - 4000 orders have RO type = 1000

I need them for a specific order number where they have the same online req number and subtracting order numbers.

Re: DAX to count order numbers in a composite model

johnt75 — Tue, 17 May 2022 16:28:38 GMT

Its iterating over the order side custom data table and for each row it is looking at the related sales orders, so the sales orders which are related to the currently iterated row. when the difference between the sales orders of the different types is < 50 it is adding 1 to the sum, essentially counting the rows where the difference is < 50

Re: DAX to count order numbers in a composite model

Syk — Tue, 17 May 2022 20:02:26 GMT

From what I understand is its evaluating per line but the nested sumx are evaluating per line with nothing to break them up. The first var 'typeRO' would sum the expression with the filters essentially saying this is the sum of this table with type = RO. so it's not a row level comparison with the 'typeAP', right?

Re: DAX to count order numbers in a composite model

johnt75 — Wed, 18 May 2022 07:58:28 GMT

No, the inner SUMXs are not evaluating the entire Sales Order table, they are only evaluating those rows which are related to the current row of order side custom data. For any single row in order side custom data it is comparing the totals of types RO and AP just for that specific order.

Re: DAX to count order numbers in a composite model

Syk — Wed, 18 May 2022 15:56:16 GMT

Okay I think I know what's happening! The 'Sales Order' table is going down to the line item level. Is there a way to keep it at just the order number level and not go row by row on order lines?

Re: DAX to count order numbers in a composite model

johnt75 — Wed, 18 May 2022 16:10:27 GMT

Not exactly sure what you're after, but inside the SUMX iteration over order side custom data you could do a SUMMARIZE on RELATEDTABLE('Sales Order') or something like that

Re: DAX to count order numbers in a composite model

Syk — Thu, 19 May 2022 13:27:43 GMT

johnt75 - Thanks for all your help so far!

I've almost got it.. I can see using DAX studio that I do have orders that are within a few of eachother.

That is using this dax query:

DEFINE MEASURE 'table'[AP order num] = CALCULATE( SUMX(SUMMARIZE(RELATEDTABLE('Sales Order'), 'Sales Order'[Order Number]),convert('Sales Order'[Order Number],INTEGER)), 'Sales order profile'[Order Type Code]= "AP",'Sales Order'[Source of Order Description]="TLP CNTL NO") MEASURE 'table'[SO order num] = CALCULATE( SUMX(SUMMARIZE(RELATEDTABLE('Sales Order'), 'Sales Order'[Order Number]),convert('Sales Order'[Order Number],INTEGER)), 'Sales order profile'[Order Type Code]= "SO",'Sales Order'[Source of Order Description]="TLP CNTL NO") MEASURE 'table'[RO order num] = CALCULATE( SUMX(SUMMARIZE(RELATEDTABLE('Sales Order'), 'Sales Order'[Order Number]),convert('Sales Order'[Order Number],INTEGER)), 'Sales order profile'[Order Type Code]= "RO",'Sales Order'[Source of Order Description]="TLP CNTL NO") MEASURE 'table'[Order number difference] = ([AP order num]+[SO order num]) - [RO order num] EVALUATE SUMMARIZECOLUMNS( 'Order Side Custom Data'[Online Req Number], "AP order num", [AP order num], "SO order num", [SO order num], "RO order num", [RO order num], "Order number difference", [Order number difference] ) ORDER BY 'Order Side Custom Data'[Online Req Number] ASC

However when I try to translate this to a single measure I'm not getting anything. Here's my attempt right now , do you see anything that stands out to you? Also, there should not be any AP or SO types on the same online req id which is why I'm adding them at the bottom (at least one should always be 0)

DEFINE MEASURE 'table'[MyMeasure] = SUMX( 'Order side custom data', VAR typeRO = CALCULATE( SUMX(SUMMARIZE(RELATEDTABLE('Sales Order'), 'Sales Order'[Order Number]),convert('Sales Order'[Order Number],INTEGER)), 'Sales order profile'[Order Type Code]= "RO",'Sales Order'[Source of Order Description]="TLP CNTL NO") VAR typeAP = CALCULATE( SUMX(SUMMARIZE(RELATEDTABLE('Sales Order'), 'Sales Order'[Order Number]),convert('Sales Order'[Order Number],INTEGER)), 'Sales order profile'[Order Type Code]= "AP",'Sales Order'[Source of Order Description]="TLP CNTL NO") VAR typeSO = CALCULATE( SUMX(SUMMARIZE(RELATEDTABLE('Sales Order'), 'Sales Order'[Order Number]),convert('Sales Order'[Order Number],INTEGER)), 'Sales order profile'[Order Type Code]= "SO",'Sales Order'[Source of Order Description]="TLP CNTL NO") VAR diff = (typeSO +typeAP)- typeRO RETURN IF(diff <=25 && diff >0 , 1) ) EVALUATE CALCULATETABLE( ROW( "MyMeasure", [MyMeasure] ) )

Re: DAX to count order numbers in a composite model

johnt75 — Thu, 19 May 2022 14:21:11 GMT

The only thing I can think of is that other columns from the order side custom data table are contributing to the filter context in the standalone measure, whereas in the SUMMARIZECOLUMNS only the [Online Req Number] is in the context.

You could try either doing the SUMX over VALUES('Order Side Custom Data'[Online Req Number]), or wrapping the whole thing in CALCULATE( ..., ALLEXCEPT('Order Side Custom Data', 'Order Side Custom Data'[Online Req Number])

Re: DAX to count order numbers in a composite model

Syk — Thu, 19 May 2022 15:23:07 GMT

I got it! It wasn't either of those - although your mention of other columns in the order side custom data table did get me thinking. I just tossed a summarize on that table and the final measure is this:

Auto Sourced Orders= SUMX( SUMMARIZE('Order Side Custom Data','Order Side Custom Data'[MRC Online Req Number]), VAR typeRO = CALCULATE( SUMX(SUMMARIZE(RELATEDTABLE('Sales Order'), 'Sales Order'[Order Number]),convert('Sales Order'[Order Number],INTEGER)), 'Sales order profile'[Order Type Code]= "RO",'Sales Order'[Source of Order Description]="TLP CNTL NO") VAR typeAP = CALCULATE( SUMX(SUMMARIZE(RELATEDTABLE('Sales Order'), 'Sales Order'[Order Number]),convert('Sales Order'[Order Number],INTEGER)), 'Sales order profile'[Order Type Code]= "AP",'Sales Order'[Source of Order Description]="TLP CNTL NO") VAR typeSO = CALCULATE( SUMX(SUMMARIZE(RELATEDTABLE('Sales Order'), 'Sales Order'[Order Number]),convert('Sales Order'[Order Number],INTEGER)), 'Sales order profile'[Order Type Code]= "SO",'Sales Order'[Source of Order Description]="TLP CNTL NO") VAR typeSOAP = typeSO +typeAP VAR diff = typeSOAP - typeRO VAR result = IF(diff >0 && diff < 50 , 1) RETURN result )

(not sure why the formatting is so bad)

Thanks for all your help johnt75