Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Good Morning!
Looking for help in an issue with two tables I have. This is an example of what my data looks like in each table.
Table1
Truck# | Reporting Party |
1 | A |
1 | B |
2 | A |
2 | A |
2 | A |
3 | A |
3 | A |
3 | B |
4 | B |
Table2
Truck # | # by reporting party "A" | # by reporting party "B" |
1 | 1 | 1 |
2 | 2 | 0 |
3 | 3 | 1 |
4 | 0 | 1 |
What i need is to be able to get the data of # by reporting party "A" and # by reporting party "B" from table 1 to exist in table 2 in order to be able to average later on in a bar chart. Any advice would be a huge help. Thank you!
Solved! Go to Solution.
Hi,
You can do it easily with power Query, just using the Group By button and then group by Truck# and Reporting Party :
The show the results in a matrix (not table), using Reporting party as column, truck as line and count as value.
And you'll get that :
Hope it helps
@ANTBressi this is the DAX code for the new calculated table:
Table2 =
ADDCOLUMNS(
VALUES(Table1[Truck#]),
"# by reporting party ""A""", CALCULATE(COUNTROWS(Table1), Table1[Reporting Party] = "A" ) + 0,
"# by reporting party ""B""", CALCULATE(COUNTROWS(Table1), Table1[Reporting Party] = "B" ) + 0
)
Huge help! Worked like a charm!
Hi,
You can do it easily with power Query, just using the Group By button and then group by Truck# and Reporting Party :
The show the results in a matrix (not table), using Reporting party as column, truck as line and count as value.
And you'll get that :
Hope it helps
Excellent, thank you!