Finding in a group of rows if value A is present but value B is not present DAX

[Anonymous]

HI All,

I am facing a situation where I need to find in a group of rows if a value A is present but value B is absent. Here is the input, the group is of Order ID.

OrderId	EntryID	Value
100	1	A
100	2	B
101	3	A
101	4	A

Output:

OrderId	EntryID	Value	A not B flag
100	1	A	False
100	2	B	False
101	3	A	True
101	4	A	True

Please help. Thanks in advance.

[amitchandak · Accepted solution]

@Anonymous , Try a new column like

new column =
var _A = countx(filter(Table, [OrderId] = earlier([OrderId]) && [Value] = "A"), [Value])
var _B = countx(filter(Table, [OrderId] = earlier([OrderId]) && [Value] = "B"), [Value])
return
if(not(isblank(_A)) && isblank(_A), true(), false())

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

Test = not(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[OrderId]=EARLIER(Data[OrderId])&&Data[Value]="B"))>0)

Hope this helps.

[amitchandak · Accepted solution]

@Anonymous , Try a new column like

new column =
var _A = countx(filter(Table, [OrderId] = earlier([OrderId]) && [Value] = "A"), [Value])
var _B = countx(filter(Table, [OrderId] = earlier([OrderId]) && [Value] = "B"), [Value])
return
if(not(isblank(_A)) && isblank(_A), true(), false())

[Anonymous]

@amitchandakThanks this worked. Only changed I did was to modify the return statement as it was only considering A.

[Anonymous]

@Jihwan_Kim @Ashish_Mathur