The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to Solution.
@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!!!
@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!!!
Instead of manually passing city = chicago and sal > 500.
is there a way to to parameterized the hard coded values?
@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))
Thanks, it worked.
I just realised this could have been done using CALCULATE function which reestablishes the relation ship between the tables.
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!!!
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