Forum Discussion
Helper IConditionally checking if a value from one column matches in another column
I have a table with data formatted like this
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?
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".
3 Replies
- Greg_Deckler
Community Champion
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".
JoshEnglishThank 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.
- Greg_Deckler
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.