Need Help - Filter tables using OR criteria from two table

Hi,

I have three tables and want to filter one out of 3 based on column values in other two tables

Here is the explaination

Table 1 --

Project ID, Resource ID, Hrs

Table 2 --

Project ID's

Table 3

Resource ID

I want to filter Table 1 using below criteria

Table 1 [Project ID] = Table 2 [Project ID] OR Table 1 [Resource ID] = Table 3 [Resource ID]

The OR part in this criteria is important. Please help.

Need Help

Anonymous

4 years ago

Hi PradeepDive ,

You can use LOOKVALUE function. However, if there is a relationship between tables, it's more efficient to use the RELATED function.

Sample data.

Table 1

Table 2

Table 3

1.If there're no relationships.

You can create a calculated table as follows.

Table =
SUMMARIZE (
    FILTER (
        ADDCOLUMNS (
            'Table 1',
            "c1",
                LOOKUPVALUE (
                    'Table 2'[Project ID],
                    'Table 2'[Project ID], 'Table 1'[Project ID]
                ),
            "c2",
                LOOKUPVALUE (
                    'Table 3'[Resource ID],
                    'Table 3'[Resource ID], 'Table 1'[Resource ID]
                )
        ),
        [c1] = [Project ID]
            && [c2] = [Resource ID]
    ),
    [Hrs],
    [Project ID],
    [Resource ID]
)

If there're relationships.

You can create a calculated table as follows..

Table 4 =
SUMMARIZE (
    FILTER (
        ADDCOLUMNS (
            'Table 1',
            "c1", RELATED ( 'Table 2'[Project ID] ),
            "c2", RELATED ( 'Table 3'[Resource ID] )
        ),
        [c1] = [Project ID]
            && [c2] = [Resource ID]
    ),
    [Hrs],
    [Project ID],
    [Resource ID]
)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Need Help - Filter tables using OR criteria from two table.pbix37 KB

7 Replies

smpa01
Community Champion
4 years ago
PradeepDive it would probably give you the same

Measure = CALCULATE ( SUM ( t1[Hrs] ),FILTER(VALUES(t1[Project ID]),t1[Project ID]=MAX(t2[Project ID]))) + CALCULATE ( SUM ( t1[Hrs] ),FILTER(VALUES(t1[Resource ID]),t1[Resource ID]=MAX(t3[Resource ID])))
- PradeepDive
  Helper II
  4 years ago
  Appreciate quick reply smpa01 but this is not what I am looking for, I want to filter the table 1 based on values in Table 2 or 3
  - smpa01
    Community Champion
    4 years ago
    PradeepDive in that case can you provide some sample data and expected output.
Anonymous
Not applicable
4 years ago
Hi PradeepDive ,

You can use LOOKVALUE function. However, if there is a relationship between tables, it's more efficient to use the RELATED function.

Sample data.

Table 1

Table 2

Table 3

1.If there're no relationships.

You can create a calculated table as follows.

Table = SUMMARIZE ( FILTER ( ADDCOLUMNS ( 'Table 1', "c1", LOOKUPVALUE ( 'Table 2'[Project ID], 'Table 2'[Project ID], 'Table 1'[Project ID] ), "c2", LOOKUPVALUE ( 'Table 3'[Resource ID], 'Table 3'[Resource ID], 'Table 1'[Resource ID] ) ), [c1] = [Project ID] && [c2] = [Resource ID] ), [Hrs], [Project ID], [Resource ID] )

If there're relationships.

You can create a calculated table as follows..

Table 4 = SUMMARIZE ( FILTER ( ADDCOLUMNS ( 'Table 1', "c1", RELATED ( 'Table 2'[Project ID] ), "c2", RELATED ( 'Table 3'[Resource ID] ) ), [c1] = [Project ID] && [c2] = [Resource ID] ), [Hrs], [Project ID], [Resource ID] )

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Need Help - Filter tables using OR criteria from two table.pbix37 KB
- PradeepDive
  Helper II
  4 years ago
  Thank Stephen Tao for the detailed explaination.
  
  My mistake, I did not explain in my question.
  There is no relationship between tables, I am trying to achieve the above result in Power query which I will later use to create a dataflow.
  
  2nd is my Table 1 is huge with 1.5M rows of data. I am not sure how efficiently the Dax summarize will work in terms of performance and speed.
  
  Any suggestions
  - Anonymous
    Not applicable
    4 years ago
    Hi PradeepDive ,
    
    Here's the solution in Power Query.
    
    1.Merge queries from two tables.
    
    You’ll get this.
    
    2.Expand columns you need.
    
    3.Add a custom column.
    
    = if [Project ID]=[Table 2.Project ID] and [Resource ID]=[Table 3.Resource ID] then 1 else 0
    
    4.Filter the rows which value is 1.
    
    5.You can remove the unneeded columns.
    
    Best Regards,
    
    Stephen Tao
    
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    
    Need Help - Filter tables using OR criteria from two table.pbix33 KB