How to rank values from different tables with DAX code

[V-lianl-msft]

Sample
NameList:

Table1:

Table2:

Scenario 1
In this scenario, I will mainly present several basic methods to find related items in another table according to the row context. Through the introduction of the following methods, we can have some insight into how to get the relevant items and better understand the scenario 2. So in this scenario, the purpose is to get total value of each name in Table1 based on the Name in related table NameList.

(1) The first way: Create a relationship between NameList and Table1

Then directly drag the Value column of Table1 into the visual of NameList.
Here is the result:

(2) The second way: In a one-to-many relationship, you can also use the Related() function to calculate it. For example, NameList and Table1 have a one-to-many relationship, so the Related()  function allows you to access Table1 through the row context of NameList.

Get total 1 = /*measure*/
CALCULATE (
SUM ( Table1[Value] ),
FILTER ( Table1, Table1[Name] = RELATED ( NameList[Name] ) )
)

(3) The third way: If no relationship is created between the two tables, you can use the filter () function. The filter () function is often used to further constrain the results of an existing filter context.

Get total 2 = /*measure*/
SUMX ( FILTER ( Table1, Table1[Name] = MAX ( NameList[Name] ) ), Table1[Value] )

After understanding the above, we have a preliminary understanding of this. Then, please look at scenario 2.

Scenario 2
This part is to filter the table and rank the values in the two tables. The principle of the following code is to create their own virtual table and then bring them together to get a dynamic union table, and then rank Value in Table1 with Value in the related dynamic union table according to Name.

For example, when I select Table1[Name] = n_1 and Table2[Type] = 1, then there are 2 rows in Table1.
• For the first row, Value = 9, I want to rank 9 with the value of the same category ctg_A in Table2, i.e. rank 9 with 2 and 10, so the rank of 9 is 2.
• For the second row, Value = 5, I want to rank 5 with the value of the same category ctg_B in Table2, i.e. rank 5 with 16, 3 and 4, so the rank of 5 is 3.

Step1: use function SELECTEDVALUE() get the selected value in slicer.
e.g. VAR _category = SELECTEDVALUE ( Table1[Category] )

Step2: Create virtual tables for the tables that need union.
Kindly note:
• The two tables must have the same number of columns.
• Columns are combined by position in their respective tables.
e.g. VAR _table1 =
SELECTCOLUMNS (
Table1,
"Name", Table1[Name],
"Category", Table1[Category],
"Value", Table1[value]
)
VAR _table2 =
SELECTCOLUMNS (
FILTER ( Table2, Table2[Category] = _category ),
"Name", Table2[Name],
"Category", Table2[Category],
"Value", Table2[Value]
)

Step3: Union tables and add a column [rank].
Step4: return the rank value according to the row context of Table1[Name]

The final DAX code is as follows:

Rank in Table2 = /*measure*/
VAR _category =
SELECTEDVALUE ( Table1[Category] )
VAR _table1 =
SELECTCOLUMNS (
Table1,
"Name", Table1[Name],
"Category", Table1[Category],
"Value", Table1[value]
)
VAR _table2 =
SELECTCOLUMNS (
FILTER ( Table2, Table2[Category] = _category ),
"Name", Table2[Name],
"Category", Table2[Category],
"Value", Table2[Value]
)
VAR _table3 =
UNION ( _table1, _table2 )
VAR _table4 =
ADDCOLUMNS ( _table3, "rank", RANKX ( _table3, [Value],, ASC ) )
RETURN
MAXX ( FILTER ( _table4, [Name] = SELECTEDVALUE ( Table1[Name] ) ), [rank] )

Related links:
Related(): Returns a related value from another table.
Filter(): Returns a table that represents a subset of another table or expression.
SELECTCOLUMNS(): Adds calculated columns to the given table or table expression.
UNION(): Creates a union (join) table from a pair of tables.
ADDCOLUMNS():Adds calculated columns to the given table or table expression.
RANKX():Returns the ranking of a number in a list of numbers for each row in the table argument.
MAXX(): Evaluates an expression for each row of a table and returns the largest value.

Author: Tang

Reviewer: Kerry Wang & Ula Huang

The method of union filtered data and then rank.pbix