filter out data based on one to one relationship

[Anonymous]

create table g_emp(empid number,ename varchar2(100));

insert into g_emp values(1,'emp1');
insert into g_emp values(2,'emp2');

create table g_emp_comp (empid number, ename varchar2(100), boss varchar2(100));

insert into g_emp_comp values(1,'emp1','emp1_boss');
insert into g_emp_comp values(2,'emp2','emp2_boss');
insert into g_emp_comp values(3,'emp3','emp3_boss');

commit;

when you create a power bi report with above 2 tables, we have a one to one created automatically based on empid

Question: when i use the boss column from g_emp_comp as a slicer, I don't want to see emp3_boss, because there is no emp3 record in the first table g_emp , how do i do that ?

[v-cgao-msft · Accepted solution]

Hi @Anonymous ,

Please new a measure.

Measure = 
VAR _ename = MAX('g_emp'[ename])
RETURN
IF(_ename<>BLANK(),1)

Then select the slicer, apply this measure to the filter of this view, and set it as shown in the figure.

Attached PBIX file for reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[Anonymous]

This is strange, my end user thinks one to one relationship is like an oracle inner join

I never thought about it in this way but is there a dax that I can use to create a new table and get only the data that matches, ( only have emp1_boss and emp2_boss as rows in the new table)

[johnt75]

create a visual level filter on either of the columns from g_emp and set it to be "not blank".

[Anonymous]

tried the below, not working

[Anonymous]

does not help,