Unable to lookup the latest date against row unique value(among two tables)

[Mughees]

Hi. I have been banging my head against this problem since two days but cannot get around this problem. I have two data tables that are linked with each other based on one column "UC code."

Table 1
UC code	Latest DONSET
11201001	x
11201002	x
11201003	x
11201004	x
11201005	x
11201006	x

Table 1 contain 3550 unique UC Codes.

Table 2
UCODE	DONSET
18101008	01-01-17
14302023	03-01-17
16501008	01-01-17
12504012	01-01-17
13101011	01-01-17
14303001	03-01-17

In table 2, there are nearly 24,000 rows and unique UCODE in aggregate are only 3550 as in table 1. Hence UCODE are repeating each time in irregular pattern. However, DONSET is different against each row UCODE. I intend to mark in table 1 the latest date of onset against UC Code present in table 2. I am unable to figure out the way. Will really appreciate guidance in this regard. I intend to find

[AlB · Accepted solution]

@Mughees 

1. Delete the relationship

2. Create the calculated  column:

Column =
CALCULATE (
    MAX ( Sheet2[Diff (Today - DONSET)] ),
    FILTER ( ALL ( Sheet2[UCODE] ), Sheet2[UCODE] = Sheet1[UC Code] )
)

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Mughees]

Thank you AIB. It worked perfectly. For my knowledge, can you please tell that why U added all after filter.

Regards

[AlB]

@Mughees 

You want to restrict the search on the second table to the UCODE you have in the current row of the first table. That is what we do with the filter. Actually an ALL is not strictly necessary here since you there is no relationship between the tables, so   DISTINCT would also work:

Column =
CALCULATE (
    MAX ( Sheet2[Diff (Today - DONSET)] ),
    FILTER ( DISTINCT ( Sheet2[UCODE] ), Sheet2[UCODE] = Sheet1[UC Code] )
)

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[AlB]

Hi @Mughees 

I understand you are looking for a calculated column in Table1? If so:

Latest DONSET =
CALCULATE ( MAX ( Table2[DONSET] ) )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Mughees]

Hi AIB

I do not want max value of the column. I want max value of date of onset for each unique UC Code. This is giving me against the whole column, i have tried it earlier but it do not work

[AlB]

@Mughees 

If there's a proper relationship as you described, it should work. The CALCULATE will trigger context transition and the filter on UC Code will propagate down to the second table.   Can you share the pbix? Or a simplified pbix with mock data that reproduces your scenario?

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Mughees]

Hi AIB

I have attached the files in the below reply to Philp. Would appreciate if you can review them

[PhilipTreacy]

Hi @Mughees 

In Table1 you create a measure like this

Latest DONSET = CALCULATE(MAX('Table2'[DONSET]), FILTER('Table2', 'Table2'[UCODE] = SELECTEDVALUE('Table1'[UC Code])))

Regards

Phil

[Mughees]

Hi Philip

No luck with this formula. I have applied it but it is also giving an error. I am attaching a POWER BI file along with excel for your reference. Would be really thank ful if you can sort it out.

Link to POWER BI and Excel file