cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
manikk
Frequent Visitor

Remove/Hide multiples rows dynamically from dataset

I have a dataset consisting of two tables

Table 1 has all the data and Table 2 consist list of exception which should not be there in Table1

 

Table 1:

Name  Contact  Address

Ram     70xxxx   Harxxxx

Simran 80xxxx   Aarxxxx

Shyam 90xxxx   Banxxx

Krish   10xxxx    Carxxxx

Tom    20xxxx    Darxxxx

 

Table 2:

Name

Ram

Krish 

Tom

 

When I create a table visual then the output should be like this.

Name  Contact  Address

Simran 80xxxx   Aarxxxx

Shyam 90xxxx   Banxxx

I want to remove all the rows dynamically from table 1 which has the same name in Table 2.

Suggest me some way to achieve this.

 

@PowerBI, @PowerQuery, @dax

1 ACCEPTED SOLUTION
speedramps
Solution Sage
Solution Sage

You can do this in Power Query of DAX

 

In Power Query you could merge using a right anti join.

 

Or create a relationshiop and create a DAX measure

exceptionflag = INT(NOT(ISEMPTY(Table2)))
this will return 1 if the Table1 has a corresponding value or null if it does not.

They in your visual use the FILTER menu to only show rows with exceptionflag not = 1 

 


Remember we are BI community voluntrees so please click the thumbs-up for me taking the trouble to help you and then accept the solution if it works.  Thank you !

View solution in original post

2 REPLIES 2
RohiniP-26
Resolver I
Resolver I

@manikk 

create a calculated column in table 1 to find the names presented in Table 2 exception list

lookup name = LOOKUPVALUE('Table 2'[name],'Table 2'[name],'Table 1'[name])
In table visual, drag and drop the 'lookup name' field in Filters on this visual pane and in basic filtering select blank option.
 
If this post helps you mark it as 'Accept as solution' below.

 

speedramps
Solution Sage
Solution Sage

You can do this in Power Query of DAX

 

In Power Query you could merge using a right anti join.

 

Or create a relationshiop and create a DAX measure

exceptionflag = INT(NOT(ISEMPTY(Table2)))
this will return 1 if the Table1 has a corresponding value or null if it does not.

They in your visual use the FILTER menu to only show rows with exceptionflag not = 1 

 


Remember we are BI community voluntrees so please click the thumbs-up for me taking the trouble to help you and then accept the solution if it works.  Thank you !

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors