Top N in related tables

[Dmalx]

I am not able to filter Top N in 2 related tables. I have to filter the latest value

So for a test I created a very simple table, the function works just fine:

Then I have 2 related tables: 'DATE'[Id],[DateTime], another table [DateId]...Values... . So there is a relationship one to many DATE[Id]->[DateId]

In the screenshot DateTime column is from table DATE the rest is from another table. I try to get the latest value "Wavelength" (79 from 8th April ). Obviously the Top N filter doesnt work.

How to fix the issue?

[V-pazhen-msft]

@Dmalx 

It would be great if you can share your sample file. I believe the problem is because there is no direct relationship between Date time table and Dateid table.

Try remove DATE[Id]->[DateId] relationship, and create a relationship between [DateId] table and data time table.

Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

Create a desc rank on date and filter on 2.

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

[Dmalx]

Thank you for the reply!

If I try to calculate rank in the new date table, f.e.

Table 3 = SELECTCOLUMNS('DATE', "DT", 'DATE'[DateTime], "DID", 'DATE'[Id])

Rank = RANKX('Table 3', 'Table 3'[DT].[Date])

The issue is till be there:

If I try to create the rank DateTime in new table

Column = RANKX('DATE', 'DATE'[DateTime])

I get the error:

A single value for column 'DateTime' in table 'DATE' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Thank you