Sort one column by another column in another table

[amaaiia]

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?

[audreygerred]

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!

[amaaiia]

I cant' join tables in DirectLake mode, it should me Import Mode.

[audreygerred]

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... 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