Forum Discussion
Populate a new calculated column with data from within the same table DAX or M?
Evening All,
I have a scenario where I have appended three data tables together, each table has a field which the others need, but don't have naturally, for example a 'How Paid'. This field exists and is populated in my revenue table but not in the costs table it is appended with, I need it populated so I can easily check profitability by each payment method.
Sample Data
| Transaction Code | How Paid | Source Table | Value |
| 555444777 | Credit Card | Revenue Table | 500 |
| 555444777 | Cost of Sales Table | -200 | |
| 555444777 | 3rd Table | -100 | |
| 888888888 | Cash | Revenue Table | 500 |
| 888888888 | Cost of Sales Table | -200 | |
| 888888888 | 3rd Table | -100 | |
| 111111111 | Debit Card | Revenue Table | 500 |
| 111111111 | Cost of Sales Table | -200 | |
| 111111111 | 3rd Table | -100 |
I need a new column, or a way of populating the blanks, for all three lines that is populated with the 'How Paid' from the Revenue Source.
Any ideas?
Thank you
Martin
Try this column
Column = CALCULATE ( FIRSTNONBLANK ( Table1[How Paid], 1 ), ALLEXCEPT ( Table1, Table1[Transaction Code] ) )
4 Replies
Try this column
Column = CALCULATE ( FIRSTNONBLANK ( Table1[How Paid], 1 ), ALLEXCEPT ( Table1, Table1[Transaction Code] ) )In Power Query
1. Dupicate the table
2. Group the new table by Transaction code and add an aggregation to be MAX over column How Paid
3. Merge the new table into the orignal table joining on Transaction code.
I have attached a sample PBIX File
Thank you both,
I am trying the DAX method at the moment, seems to be working just doing some testing and I'll accept as solution. Will try the Query solution soon after.
Thank you
Martin
Here is an alternative DAX column. I found the FIRSTNONBLANK has issues with some datasets
Column = MAXX( FILTER( 'Table1', 'Table1'[Transaction Code] = EARLIER('Table1'[Transaction Code]) ), [How Paid] )
