Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
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.
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
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.
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.
@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])))
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
@PradeepDive in that case can you provide some sample data and expected output.