cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RsRoel
New Member

Filter records that have value X but not Y

I've been breaking my head about the following: I'd like to filter ID's that have certain values but not others. E.g.

 

IDValue
1A
1B
1C
2A
2C
2D
3C
3D
3E

 

What I would like to happen here is filter the ID's that have A, but do not have B. So In the end you have the output: 

IDValue
2A
2C
2D

 As ID 1 has A and also B, and ID 3 does not have B, but also not A. 

 

Anyone an idea how to solve this? 

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@RsRoel create a measure for condition and then use it in visual level filter or add it in the table visual

Filter = 
VAR __val = CALCULATETABLE ( VALUES ( 'Table'[Value] ),  ALLEXCEPT ( 'Table', 'Table'[ID] ) )
RETURN
IF ( "A" IN __val && NOT "B" IN __val, "Y" )

 

 

image.png






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

2 REPLIES 2
parry2k
Super User
Super User

@RsRoel create a measure for condition and then use it in visual level filter or add it in the table visual

Filter = 
VAR __val = CALCULATETABLE ( VALUES ( 'Table'[Value] ),  ALLEXCEPT ( 'Table', 'Table'[ID] ) )
RETURN
IF ( "A" IN __val && NOT "B" IN __val, "Y" )

 

 

image.png






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@RsRoel use it as a filter

 

image.png






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!