Forum Discussion

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

CALCULATETABLE with filter on different table

Hi experts!

I would like to create a new table by using CALCULATETABLE.

Here I would like to get all articles from table 1 if the same article has the status "TRUE" in table 2.

How would you do that?

This approach is not working:

 

Test = 
VAR _Article = CALCULATE(SELECTEDVALUE('Table2'[Article]), 'Table2'[Status] = TRUE())
RETURN
FILTER(CALCULATETABLE(ADDCOLUMNS(SUMMARIZE(Table1, Table1[Article], Table1[OrderNr]), "Value", Table1[Value])), Table1[Article] =_Article)
Need Help

8 Replies

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

      tamerj1 : Not directly. They are related to a common dimension table that contains unique articles.

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

        Hi joshua1990 
        Please try

        Tabletest =
SUMMARIZECOLUMNS (
    DimTable[Article],
    Table1[OrderNr],
    "Value",
        CALCULATE (
            SUM ( Table1[Value] ),
            Table2[Status] = TRUE (),
            CROSSFILTER ( Table2[Article], DimTable[Article], BOTH )
        )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi

    To filter the table Table1 based on the values in Table2, you can use the following expression:

     


    Test = FILTER( CALCULATETABLE( ADDCOLUMNS(SUMMARIZE(Table1, Table1[Article], Table1[OrderNr]), "Value", Table1[Value]) ), Table1[Article] IN ( SELECT Table2[Article] FROM Table2 WHERE Table2[Status] = TRUE() ) )
     

    This expression first calculates the summary table of Table1, adding a new column Value that contains the values from the Value column of Table1. It then filters this summary table based on the condition that the Article column of Table1 must be present in the list of articles that have Status set to TRUE in Table2.

     

    I hope this helps and mark as solved! Let me know if you have any questions.

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

      Anonymous : Thanks a lot, but I am not able to add SELECT FROM WHERE into the function.

      Is this correct?