Need Help - Filter tables using OR criteria from two table

[PradeepDive]

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.

[v-stephen-msft · Accepted solution]

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.

[PradeepDive]

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

[v-stephen-msft]

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.

[PradeepDive]

Thanks Stephen,

I am already using the same approach currently, but as my tables are huge it takes lots of time for merging. I was looking for something which can use to get the output in less and if possible in single line (but not necessary)

Appreciate your time to answer my query though. 

[smpa01]

@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]

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]

@PradeepDive  in that case can you provide some sample data and expected output.