cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JoshEnglish Regular Visitor
Regular Visitor

Conditionally checking if a value from one column matches in another column

I have a table with data formatted like this

image.png

 I need to create the ShouldSkip column using the following logic:

 

ShouldSkip should be TRUE only when isPartial on that line is True AND the value in the Primary column also appears in the Account column as long as the Agent value is the same.

 

So the first line in ShouldSkip has a value of FALSE because isPartial is False in that line.

The second line should be True because the table has a row with "A" in Agent and "Here" in account.

The third line should be False because the table does not have a row with "B" in Agent and "Utopia" in Account.

 

I tried 

CalculatedSkip = if([isPartial],
CALCULATE(COUNTROWS(Table1),Table1[Agent]=[Agent],Table1[Account]=[Primary])>0,
FALSE) 

but that just errors out.

 

I'm an experienced Python programmer, so DAX is throwing me for a loop on its logic.

 

Any suggestions on making this work?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Conditionally checking if a value from one column matches in another column

Column = IF([isPartial] 
&& CALCULATE(COUNTROWS(aShouldSkip),
FILTER(aShouldSkip,aShouldSkip[Agent]=EARLIER(aShouldSkip[Agent])
&& aShouldSkip[Account]=EARLIER(aShouldSkip[Primary]))) >= 1,
TRUE()
,FALSE()
)

aShouldSkip is my table name. Don't feel bad, took me a while to wrap my head around EARLIER until I finally realized it was just a way of saying "current value".


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

View solution in original post

3 REPLIES 3
Highlighted
Super User IV
Super User IV

Re: Conditionally checking if a value from one column matches in another column

Column = IF([isPartial] 
&& CALCULATE(COUNTROWS(aShouldSkip),
FILTER(aShouldSkip,aShouldSkip[Agent]=EARLIER(aShouldSkip[Agent])
&& aShouldSkip[Account]=EARLIER(aShouldSkip[Primary]))) >= 1,
TRUE()
,FALSE()
)

aShouldSkip is my table name. Don't feel bad, took me a while to wrap my head around EARLIER until I finally realized it was just a way of saying "current value".


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

View solution in original post

JoshEnglish Regular Visitor
Regular Visitor

Re: Conditionally checking if a value from one column matches in another column

Thank you. I have to admit none of it makes sense. Some functions only seem to work in certain contexts. The tool tip for Earlier makes it sound like it does something completely different than what it does.

Super User IV
Super User IV

Re: Conditionally checking if a value from one column matches in another column

You are correct on EARLIER, no doubt. EARLIER really only works in a table context, which typically means "not a measure" unless you are using something tricky like one of the "X" functions. And, I agree, the description of EARLIER is horrendous. But, if you think of it as "current value" you should be fine.


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors