Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have an employee table that links to 3 other tables on EMP#. The employee table has all employees where as the other tables only have a smalll portion. I want to add an employee filter to a report page, however if I straight up use the employee table I will end up with a lot of extra values in the filter that lead to no data. I cant use the emp# from any of the linked tables because there is no gaurentee they share the same employee.
All of my relationships are linked 1way from the employee table.
I was trying to build a countrows filter in the employee table to the other tables but I either get a number that is the total pop or 1 no matter what. I tried defining the column below in the EmpTbl. It returns the total pop of Tbl1 in every cell.
InTables = COUNTROWS( FILTER( 'Tbl1','Tbl1'[EMP#]=RELATED( 'EmpTbl'[EMP#]) ) )
As an aside I have a business unit table that bridges those same table, this originally create amiguity issues until I made all relationships 1way.
Solved! Go to Solution.
So I created the following column in the EMPTbl and then added it (InTables = Y) as a filter of the filter on my report page.
I am still running into the issue of BlankRow not returning N, but I dont really care about the no's. I could probably nest it in somthing.
InTables = IF( COUNTROWS(FILTER('Tbl1','Tbl1'[EMP#]='EmpTbl'[EMP#])+ COUNTROWS(FILTER('Tbl2','Tbl2'[EMP#]='EmpTbl'[EMP#])+ COUNTROWS(FILTER('Tbl3','Tbl3'[EMP#]='EmpTbl'[EMP#])>0 ,"Y","N")
So I created the following column in the EMPTbl and then added it (InTables = Y) as a filter of the filter on my report page.
I am still running into the issue of BlankRow not returning N, but I dont really care about the no's. I could probably nest it in somthing.
InTables = IF( COUNTROWS(FILTER('Tbl1','Tbl1'[EMP#]='EmpTbl'[EMP#])+ COUNTROWS(FILTER('Tbl2','Tbl2'[EMP#]='EmpTbl'[EMP#])+ COUNTROWS(FILTER('Tbl3','Tbl3'[EMP#]='EmpTbl'[EMP#])>0 ,"Y","N")
uhg I think I found it, not that I totally understand. I dropped the related function. I think that was pushing the perspective out to the other table and then summing the number of rows that found a match back ie the total pop because indeed there is a match.
I'm not going to resolve this until I confirm I didn't create some other mess. I also need to deal with the fact that when the filter returns no rows I am getting a blank. I would prefer a 0.
InTables = COUNTROWS( FILTER( 'Tbl1','Tbl1'[EMP#]='EmpTbl'[EMP#] ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
82 | |
70 | |
63 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |