Forum Discussion
Sort one column by another column in another table
Hi.
I have one fact table and one dimension table in the semantic model, related by City field:
Fact:
| City | Year | Value |
| Madrid | 2022 | 3 |
| Madrid | 2023 | 5 |
| Madrid | 2024 | 6 |
| Barcelona | 2022 | 8 |
| Barcelona | 2023 | 4 |
| Barcelona | 2024 | 1 |
Dimension:
| City | Order |
| Madrid | 1 |
| Barcelona | 2 |
In the report, I have a Pivot Table as follows:
| Barcelona | Madrid | |
| 2022 | 3 | 8 |
| 2023 | 5 | 4 |
| 2024 | 6 | 1 |
I want to order pivoted City column with Dimension[Order] columns, so in that case Madrid is 1 and Barcelona is 2 (more Cities can be added). So the pivoted table would be as follows:
| Madrid | Barcelona | |
| 2022 | 8 | 3 |
| 2023 | 4 | 5 |
| 2024 | 1 | 6 |
The semantic model is in DirectLake mode. I know one approach is to add Order column in fact table, but this is not a good idea because this order might change, that's why I prefer having a dimension table.
Any ideas?
3 Replies
Hello! You can achieve this by joining your dimension table to your fact. Then, in the dimension table, click on city field, go to the Column Tools ribbon, go to Sort by Column and select Order field. Next, in your matrix visual, add in what you want to rows (year) and put city from your dim table in columns and your measure in values.
Viola!
I cant' join tables in DirectLake mode, it should me Import Mode.
As far as I know, you can create realtionships with tables in direct lake mode in power bi - see this article, the relationships are there are the tables are direct lake. Leveraging pure Direct Lake mode for maximum query performance | Microsoft Power BI Blog | Microsoft Power BI And, since star schema is still recommended in direct lake, relationships/joins are needed: Define relationships in data models for data warehousing - Microsoft Fabric | Microsoft Learn
Define relationships in data models for data warehousing - Microsoft Fabric | Microsoft Learn
