CALCULATETABLE with filter on different table

[joshua1990]

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)

[Anonymous]

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]

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

Is this correct?

[tamerj1]

Hi @joshua1990 

are they related?

[joshua1990]

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

[tamerj1]

Hi @joshua1990 
Please try

Tabletest =
SUMMARIZECOLUMNS (
    DimTable[Article],
    Table1[OrderNr],
    "Value",
        CALCULATE (
            SUM ( Table1[Value] ),
            Table2[Status] = TRUE (),
            CROSSFILTER ( Table2[Article], DimTable[Article], BOTH )
        )
)

[joshua1990]

@tamerj1 : Awesome, that works perfectly fine! Thanks a lot.

What if I want to add multiple value columns from both table 1 and table 2 into this?

How would you do that?

[tamerj1]

@joshua1990 

You can try following the same CALCULATE statement for all calculations. 

[joshua1990]

@tamerj1 : Thanks, but that would mean I have to add the filter in every CALCULATE, right?