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.
Hi Community,
I want to compose/ merge tables and combine their values. I am looking for a method that uses calculated columns (dax) or power query.
There is a simpliefied scenario:
- Table A contains Source Data with Cost
- Table B contains User Date based upon the source Data
- A third Table A+B should be created. Look at the image example.
Table A:
Table B:
Result Table A+B
Solved! Go to Solution.
Hi @raymond -
In Power Query, you could do the following:
Hi,
This calculated column formula in Table A will work
=CALCULATE(SUM('Table B'[User]),FILTER('Table B','Table B'[Date]=EARLIER('Table A'[Date])&&'Table B'[Source]=EARLIER('Table A'[Source])))
Hope this helps.
Hi @raymond -
In Power Query, you could do the following:
hi @Anonymous , thank you. That was easy. Say do you know a way to do it with a calculated column too?
@raymond - There are ways to do this, but you wouldn't want to bring a table into Power BI unless you need it there for another purpose.
One way you could do this is:
1. Create a Custom Column in Power Query on each table - concatenate the date and source.
2. Create a Relationship between the tables in Power BI - use the concatenated column for the join.
3. Create a Calculated Column like this:
Sum Of User =
SUMX(
RELATEDTABLE('OtherTable'),
'OtherTable'[User]
)
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |