Forum Discussion

Applicable88's avatar
Applicable88
Icon for Impactful Individual rankImpactful Individual
4 years ago
Solved

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 PositionShippingDate

 

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. 

 

 

 

  • Anonymous's avatar
    Anonymous
    4 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))
    return
    SUMMARIZE(_except,[Date],"sale",SUM(ShippingTable[Sales]),"position",SUM(ShippingTable[Position]),"_count",DISTINCTCOUNT(ShippingTable[OrderNo]))
     
    Best Regards,
    Jay

4 Replies

  • Anonymous's avatar
    Anonymous
    Not 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))
    return
    SUMMARIZE(_except,[Date],"sale",SUM(ShippingTable[Sales]),"position",SUM(ShippingTable[Position]),"_count",DISTINCTCOUNT(ShippingTable[OrderNo]))
     
    Best Regards,
    Jay
    • Applicable88's avatar
      Applicable88
      Icon for Impactful Individual rankImpactful 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.