Forum Discussion
Create Column on transactions Level based on transactions Level
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
Anonymous,
You may refer to the DAX below.
Column = CONTAINS ( Table1, Table1[TransactionID], Table1[TransactionID], Table1[R-Price], 0 )
4 Replies
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
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:
Thanks for your reply.
Laz
Anonymous,
You may refer to the DAX below.
Column = CONTAINS ( Table1, Table1[TransactionID], Table1[TransactionID], Table1[R-Price], 0 )
