Forum Discussion
how 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 | 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.
- Anonymous4 years ago
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
4 Replies
- AnonymousNot applicable
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- Applicable88
Impactful Individual
Anonymous 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
Super User
Applicable88 , A measure like
Calculate ( Countrows (except( ShippingTable[OrderNo] , OrderTable[OrderNo] ) ) )
- Applicable88
Impactful Individual
amitchandak , sorry, but thats not going to add the columns that I need.