how to correctly add columns from a table visual?

[Applicable88]

Hello,

I want to do a calculated table, because I have problems with circular dependencies.

Sample table:

The shipping table: 

OrderNo.		Date		Status		Sales		Position
1111		10/01/2022		S		1000		333
1112		10/01/2022		S		1000		111
1113		10/01/2022		S		1000		777
1119		10/01/2022		S		1500		666
1118		10/01/2022		S		1500		999
1000		10/01/2022		S		3000		222

The preparation table:

OrderNo.		Date		Status		Sales		Position
1111		09/01/2022		P		1000		333
1112		09/01/2022		P		1000		111
1113		09/01/2022		P		1000		777
1114		09/01/2022		P		8000		123
1115		09/01/2022		P		10000		321
1116		09/01/2022		P		1500		543

Calculated Tables = 
Var _CurrentDate = 
Max ( ShippingTable[Date] )

Var _LatestPreparationDate = 
Calculate ( 
    Max ( PreparationTable[Date] ),
    PreparationTable[Date] < _CurrentDate 
)

Var _Orders_S =
Calculatetable (
    Values ( ShippingTable[OrderNo] ) , 
    ShippingTable[Status] = "S",
    PreparationTable[Date] = _LatestPreparationDate
)

Var _Orders_P =
Calculatetable (
    Values ( PreparationTable[OrderNo] ) , 
    PreparationTable[Status] = "P"
)

Return
Calculate ( 
    Count ( ShippingTable[OrderNo] ),
    Except (
        _Orders_S,
        _Orders_P
    )
)

 The top part is correct, but instead using a measure like the last part:

Calculate ( Count ( ShippingTable[OrderNo] ), Except ( _Orders_S, _Orders_P )

How to use the part "Except ( _Orders_S, _Orders_P )", but also having the right sum of  sales and sum of position in the same table for every shipping date?

When I doing this:

Caluclatetable ( 

Except ( _Orders_S, _Orders_P ))

The return of the table only consist of the OrderNo., but not the rest of the table. 

My table that should be returned should have the the sales, position and shipping date that were filtered through the except function like :

Sales

Position

ShippingDate

I tried with addcolumns as well, but I guess the lineage is broken, so it won't filter right sales and positions. Not even with context transition

I hope someone has a solution here.

Thank you very much in advanced.

Best. 

[v-jayw-msft · Accepted solution]

Hi @Applicable88 ,

Check the formula.

Calculated Table =

Var _CurrentDate =

Max ( ShippingTable[Date] )

Var _LatestPreparationDate =

Calculate (

Max ( PreparationTable[Date] ),

PreparationTable[Date] < _CurrentDate

)

Var _Orders_S =

Calculatetable (

Values ( ShippingTable[OrderNo] ) ,

ShippingTable[Status] = "S",

PreparationTable[Date] = _LatestPreparationDate

)

Var _Orders_P =

Calculatetable (

Values ( PreparationTable[OrderNo] ) ,

PreparationTable[Status] = "P"

)

var _except = FILTER(ShippingTable, ShippingTable[OrderNo] in EXCEPT(_Orders_S,_Orders_P))

return

SUMMARIZE(_except,[Date],"sale",SUM(ShippingTable[Sales]),"position",SUM(ShippingTable[Position]),"_count",DISTINCTCOUNT(ShippingTable[OrderNo]))

Best Regards,

Jay

[Applicable88]

@v-jayw-msft thank you very much!

I didn't know it was possible to use the "IN" operator refering to single columns like this:

 var _except = FILTER(ShippingTable, ShippingTable[OrderNo] in EXCEPT(_Orders_S,_Orders_P))

I only used it as specific string lookups in columns so far.

Thank you very much.

Best. 

[amitchandak]

@Applicable88 , A measure like

Calculate ( Countrows (except( ShippingTable[OrderNo] , OrderTable[OrderNo] ) ) )

[Applicable88]

@amitchandak , sorry, but thats not going to add the columns that I need.