Forum Discussion

joshua1990's avatar
joshua1990
Icon for Post Prodigy rankPost Prodigy
3 years ago

Calculated column with filter on 2 different tables

Hi experts!

I have a transactional table that shows me the sales for each article for each week.

Now I would like to get the number of orders for each article. This is stored in a different table. This I would like to add as a calculated column into the first table.

How can I add this value into the first table?

Would you do that with a CALCULATE(SUM(..), FILTER Calendar, Filter Order?

Or how would you do that?

Need Help

4 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    joshua1990 
    It depends on your data model and relationships. Please share a screenshot or clarify.

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi joshua1990 

    supposing two tables are related, typically it works like:

    Column = COUNTROWS(RELATEDTABLE(OrderTable))

    • joshua1990's avatar
      joshua1990
      Icon for Post Prodigy rankPost Prodigy
      3 years ago

      FreemanZ : Thanks, but both tables are linked to a dimensional table that contains all articles and then also to a calendar. 

      • FreemanZ's avatar
        FreemanZ
        Icon for Super User rankSuper User
        3 years ago

        hi joshua1990 

        In your transaction table, what if you try to add a column like:

        Column = 
VAR _article = TransactionTable[article]
RETURN
COUNTROWS( FILTER( OrderTable, OrderTable[article] = _article) )