Populate a new calculated column with data from within the same table DAX or M?

[Martin_Bruwer]

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

[Martin_Bruwer]

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

[Phil_Seamark]

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]
        )

[Phil_Seamark]

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

[Zubair_Muhammad · Accepted solution]

Hi @Martin_Bruwer

Try this column

Column =
CALCULATE (
    FIRSTNONBLANK ( Table1[How Paid], 1 ),
    ALLEXCEPT ( Table1, Table1[Transaction Code] )
)