Sort by ascending with nulls from a relationship at the bottom

[trevordunham]

I have two tables, table 1 is a list of IDs and their details. Table 2 is a list of IDs and their rank. I have a relationship between these two tables based on ID. I want to sort all of the IDs and all of their details by the rank ascending with the nulls at the bottom, currently the nulls are at top by default. The nulls come from IDs in table 1 that are not in table 2. I tried to create a custom column that brings in 2 for all with rank null else 1 and sort by that. This doesn't work however as it is just bringing in nulls still for the custom column where rank is null for IDs in table 1 that are not in table 2.

Any ideas?

[trevordunham · Accepted solution]

I was able to solve this with the following DAX:

RankFromTableA =
IF(
ISBLANK(RELATED('Table A'[Rank])),
MAX('Table A'[Rank]) + 1, 
RELATED('Table A'[Rank])
)

[Anonymous]

Hi,@trevordunham 

We are glad that you have found a solution and are willing to share it, and accept your post for a solution, which is very helpful to our community, and community members who have the same problem as you will find a solution faster.

Thank you for your contribution to the community and wish you all the best in your work.

Best Regards,

Leroy Lu