DAX to count order numbers in a composite model

[Syk]

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!

[johnt75]

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

[Syk]

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?

[johnt75]

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.

[Syk]

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?

[johnt75]

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

[Syk]

@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]
    )
)​

[johnt75 · Accepted solution]

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])

[Syk]

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  

[johnt75]

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 )
)

[Syk]

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.