Create Column on transactions Level based on transactions Level

[Anonymous]

Hi Community,

So what i am trying to achieve is the following:

TransactionID	ItemID	R-Price	FLAG
37700791	0505466191986	21.31	Y
37700791	0505466186054	21.3	Y
37700791	0505466186089	0	Y
37700895	0505466174564	25	N
37700895	050546694563	22.5	N

As you can see, i have a table with TransactionID, ItemID and the Sales Price (R-Price).

I need to create a FLAG column which can be used as a Filter to include or exclude all TransactionID's that have at least one item with a 0 value.

I have managed to achieve this in Power Query. However, the performances are slow. Can anyone give me any direction on how to do this in DAX?

Thanks,

Laz

[v-chuncz-msft · Accepted solution]

@Anonymous,

You may refer to the DAX below.

Column =
CONTAINS (
    Table1,
    Table1[TransactionID], Table1[TransactionID],
    Table1[R-Price], 0
)

[Anonymous]

Thank you for this 🙂

[LivioLanzo]

hI @Anonymous

what is the logic behing the flag? Are you extracting the data from a databse? this could be pused back to the database engine.

If you need to do this in Power Query, then it is faster if you create a mapping table with the logic built in and then create a Merge

[Anonymous]

Hi @LivioLanzo,

The logic is, we have some items in transactiosn which have a 0 Sales Value, which we identified as being the case due to a particular discount being used. So i want to do analysis with excluding all transactions whereby a single item has a 0 value. Thats the need for this flag.

Also, the database are coming from CSV Files. Currently that is exactly what i am doing, creating a lookup table with logic built in (corrrect keys to create merge) and then do a LEFT OUTER MERGE. 

However, it all works a tad slow which is why i am looking for altertive solutuons. This will make a lot more sense if you see the previous post which is in relation:

https://community.powerbi.com/t5/Desktop/Transformations-in-Power-Query-Very-Slow-Performance/m-p/58...

Thanks for your reply.

Laz