Ranking comments in direct query mode

[_Jo_]

Hi guys,

I’m trying to find a formula to rank comments in a table (e. g. P&L statement) using direct query. I have the following tables that I need to bring together for it:

Comment table:

• commentId
• comment
• periodId
• legalEntityId
• accountId

Account table:

• accountId
• accountName1
• accountName2
• accountName3
• accountSort1
• accountSort2
• accountSort3

Account balance table:

• accountBalanceId
• balance
• accountId
• periodId
• legalEntityId

Period table:

• periodId
• period
• year

Legal Entity table:

• legalEntityId
• legalEntity

The table comment is not connected to the table account balance but has to all other tables a one-to-many relationship. Furthermore, the account table includes a hierarchy. accountName 1 is the lowest level (1). accountSort 1 sorts accountName1 (to avoid alphabetical ordering).

Assume the following table/ P&L statement in my report:

			2023	Comment#
		Total project income	10
		Total project expenses	-5
	Total project		5
	Total operating expenses		-1	1
	Total other expenses		-1
	Total interest result		2	2
Ordinary result before tax			5
[accountName3]	[accountName2]	[accountName1]	[balance]	DAX formula?

Furthermore, I have a comment table in my report:

Comment#	Comment
1	Due to less rental costs
2	Due to….
DAX formula?	[comment]

How can I rank my comments using direct query? The result should be like Comment#. The ranking has to change when choosing a different year or different legal entity via slicer because there might be comments to different accounts. I thought about using the sort columns from the account table somehow for it but I didn’t get far…