OR feature triggered by key

[Yahya]

Hey community,

I'm trying to build a measure for which I don't know if I need it to be in Power Query, Calculated Column or measure.

On a similar table, this is what I'm trying to do

This is the table with the 'many' keys. 

Key	Value
12345	null
12345	SWAP
54321	null
54321	null
78945	null
78945	null
78945	SWAP

Once a Key has the value SWAP, the other same Keys need to be considered a SWAP type

I have tried to use the OR feature as a measure, but I get strange results

Can you guys help me out with this?

With Regards,

Y.

[Fowmy · Accepted solution]

@Yahya 

Add this as a new column in Data Model:

Swap_Key = 
VAR _Adv = CALCULATE(
    COUNTROWS('Table (4)'),
    'Table (4)'[Value]="SWAP")
RETURN

IF(_Adv>0,"SWAP",BLANK())

If you are satisfied with my answer, please mark it as a solution so others can easily find it.

Don't forget to give KUDOS ? to replies that help answer your questions

Subscribe to ExcelFort: Learn Power BI, Power Query and Excel

[Yahya]

Hey Fowmy,

Thank you for your reply. This seems to work as expected 🙂 

[amitchandak]

@Yahya , what is the output you want

[Yahya]

Hey amitchandak,

Thanks for the quick reply

The output I'd like is the following

When the calculation is done, filter out SWAP and distinct count the keys

and I'll make a 2nd measure to filter out the ones that aren't SWAP, in this case Blanks (null), and do the same distinct count of keys.

[amitchandak]

@Yahya , Try like

calculate(distinctcount(Table[key]),isblank(Table[Value]))

Or
calculate(distinctcount(Table[key]),Table[Value] ="SWAP")

[Yahya]

How can I make sure this is correct?

If I do the first, I'll get the blank values, but a key may have 2 switches, either null or "SWAP". The result of my table should be

Key	Value	result
12345	null	SWAP
12345	SWAP	SWAP
54321	null	null
54321	null	null
78945	null	SWAP
78945	null	SWAP
78945	SWAP	SWAP