Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Try this column
Column = CALCULATE ( FIRSTNONBLANK ( Table1[How Paid], 1 ), ALLEXCEPT ( Table1, Table1[Transaction Code] ) )
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] )
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
Try this column
Column = CALCULATE ( FIRSTNONBLANK ( Table1[How Paid], 1 ), ALLEXCEPT ( Table1, Table1[Transaction Code] ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |