Help with RANKX and column from related table

[Anonymous]

Look below for a picture showing the values in two tables (Survey on the left, National Rank on the right). The relationship is the "Standard" column but not all survey standards have national ranks. In this case, there are only five standards with national ranks.

I added two measures:

Observation Count (how many times a Standard value appears in the Survey table). ABC appears 3 times in the Survey table.

Rank of Standard (used RANKX to calculate the rank based on the Observation Count). ABC has the most with 3, so it is ranked 1.

When I add a table visualization and display data only from the Survey table, the list appears correctly. (Middle image). Note that I have a filter to display only rows where "Rank of Standard" is less than 4.

But when I add the National Rank in the bottom table, the visual displays ABC five times with a rank of 1 through 5. Obviously this isn't what I want to display. I just want the relationship to find ABC in the National Rank table, display ABC once, and display "5" in the National Rank column. BCD should show 4, and DEF should show 2.

Is there a way to make this work?

Thanks!

[v-jiascu-msft]

Hi @Anonymous,

Could you please mark the proper answers as solutions?

Best Regards,

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

The easiest way to solve this issue is changing the "Cross filter direction" of the relationship to "both" and setting the aggregation of the [National Rank] to "maximum" or "minimum". Please refer to the snapshot below.

Best Regards,