Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Filter columns from two or more tables

I have two tables Employee and Department, and the common column is deptno.
I have been trying to calculate the number of employees from deptno 20 whose earns more than 550.
I palyed with the FILTER function, but its not letting me filter columns from two different tables

 EMPNO DEPTNO SAL SZ190 10 459 SZ181 20 600 SZ876 20 587 SZ568 20 545 SZ732 30 390

Department Table

 DEPTNO CITY 10 LA 20 CHICAGO 30 JACKSONVILLE

Thanks,
Nawaz

1 ACCEPTED SOLUTION
Super User

@cmpbn

Create a lookup column and use it to filter EMP table. I have updated the PBIX with altered solution.

Does this help?

If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

Follow me on LinkedIn!!!

6 REPLIES 6
Super User

@cmpbn

Create a lookup column and use it to filter EMP table. I have updated the PBIX with altered solution.

Does this help?

If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

Follow me on LinkedIn!!!

New Member

Instead of manually passing city = chicago and sal > 500.
is there a way to to parameterized the hard coded values?

Super User

@cmpbn

For City you can use a slicer of city name[create a city  dim table and keep this disconnected], For salary create a numeric parameter. Use selectedvalue function to pass both of these to the measure. It will look something like below.

calculate(count(emp),city=selected value of(city_fromdim table)&&salary>selectedvalue(numeric_parameter))

New Member

Thanks, it worked.
I just realised this could have been done using CALCULATE function which reestablishes the relation ship between the tables.

Super User

hi @cmpbn

Below file has 2 variations of solutions, Let me know if this is what your are expecting. Else share sample output.

If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

Follow me on LinkedIn!!!

New Member

Thanks for the reply,  I miss typed my previous question.
I wanted to find out the number of employees from CHICAGO area who earns more than 550

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors