cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AleksanderPunt
Helper I
Helper I

How to check if value isn't present

My table contains 20.000 names, where each name have several registrations based on which fruits they ate. So the table has 50.000-60.000 lines of data. I want to find out who did NOT eat a specific fruit? This is just a mockup to showcase the basic problem of my real table, which isn't about fruits 🙂

 

Is there a formula to retrieve the names of those who didn't eat an orange?

 

NameFruit

John

Banana
JohnOrange
AdamApple
AdamBanana
AdamOrange
LisaApple
ChrisBanana
ChrisApple
1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@AleksanderPunt 

Create this measure and assign it to the visual filter and set it equal to zero.

Measure = INT(MAX(Table2[Name]) in CALCULATETABLE( VALUES(Table2[Name]) , Table2[Fruit] = "Orange"))

Fowmy_0-1624347248068.png

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

3 REPLIES 3
AleksanderPunt
Helper I
Helper I

Thanks, it looks like the solution from @Fowmy does the trick. Your approach @BernatAgullois unfortunately out of my knowledge on how to achieve - I haven't used expressions editors, DAX Query's etc.. but thanks anyway! 🙂

BernatAgullo
Frequent Visitor

I recommend you to create a Name table and a Fruit Table not to fall (ever) in auto-exist. 

Here my approach (very similar to Fowmy's)

noEat.JPG

Fowmy
Super User
Super User

@AleksanderPunt 

Create this measure and assign it to the visual filter and set it equal to zero.

Measure = INT(MAX(Table2[Name]) in CALCULATETABLE( VALUES(Table2[Name]) , Table2[Fruit] = "Orange"))

Fowmy_0-1624347248068.png

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

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