Forum Discussion
Applicable88
Impactful Individual
Impactful Individualhow to correctly add columns from a table visual?
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 ...
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))returnSUMMARIZE(_except,[Date],"sale",SUM(ShippingTable[Sales]),"position",SUM(ShippingTable[Position]),"_count",DISTINCTCOUNT(ShippingTable[OrderNo]))Best Regards,Jay
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
Applicable88Impactful Individual
Impactful IndividualAnonymous 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.