Forum Discussion
Creating a Calculated table with the values of another table
Hello All,
I'm looking to create a new table in my power BI data model whereby the values in a column from the first table become the columns of the other table (like a pivot table), however, I want to use the second table as a table in the model not the matrix visual in the report. what I want to accomplish is effectively below. I want to create measures based on Table 2.
Table 1
| Ticket | Task | Complete Date |
| Ticket1 | Task1 | May1 |
| Ticket1 | Task2 | May2 |
| Ticket1 | Task3 | May3 |
| Ticket2 | Task1 | may2 |
Table 2
| Ticket | Task1 | Task2 | Task3 |
| Ticket1 | May1 | May2 | May3 |
| Ticket2 | May2 |
Hello,
Thank you for the reply, I actually found my solution here:
https://community.powerbi.com/t5/Desktop/Calculated-table-with-columns-based-on-the-values-from-another/m-p/370832#M168106Pivoting one of the columns with a max date as the aggregate value is what I was looking for.
5 Replies
- kentyler
Solution Sage
In Power Query you can create a "reference" copy of your Table 1, and then unpovit the reference copy...which will give you a table like table 2 that will appear in your data model and that you can use just like any other table.... it will be updated everytime you refresh your data sources.
- amitchandak
Super User
Anonymous , not very clear, see if concatenatex can help
https://docs.microsoft.com/en-us/dax/concatenatex-function-dax
Hello,
Thank you for the reply, I actually found my solution here:
https://community.powerbi.com/t5/Desktop/Calculated-table-with-columns-based-on-the-values-from-another/m-p/370832#M168106Pivoting one of the columns with a max date as the aggregate value is what I was looking for.
v-lili6-msftCommunity Support
hi Anonymous
It's pleasant that your problem has been solved, 😁 please accept my reply as answer, that way, other community members would easily find the solution when they get same issues.
Regards,
Lin
