How do I return only the first occurrence of a value in a table and all data in that row?

[aallman]

I have a table (Table1) with 2 unique identifiers, ReferenceID and ManufacturingID. ManufacturingID is unique and each only shows up once in the table, while some ReferenceIDs have mulitple ManufacturingIDs. This table is related to another table (Table2) by ReferenceID in a many to one relationship. (ManufacturingID does not exist in the second table) 

What I need to do is pull a few values from Table1 into a visual that currently only has values from Table2. I can't do this because of the relationship. What I want is to be able to pull in only the first line for each ReferenceID from Table1 and use those values in my visual. Is this possible?

Example Table1:

ReferenceID	ManufacturingID	Date	Status
1000	1	5/5/23	Fail
1000	2	5/7/23	Pass
2000	3	5/5/23	Pass
3000	4	5/7/23	Fail
3000	5	5/8/23	Pass

Example Table2:

ReferenceID	Department	Type
1000	Main	Standard
2000	Main	SameDay
3000	Platinum	Standard

Desired Output visual:

ReferenceID	Department	Type	Date	Status
1000	Main	Standard	5/5/23	Fail
2000	Main	SameDay	5/5/23	Pass
3000	Platinum	Standard	5/7/23	Fail

[Ashish_Mathur]

Hi,

Create a Calendar Table and a relationship (Many to One and Single) from the Date column of Table1 to the Date column of the Calendar Table.  To your visual, drag the first 3 columns from Table2.  Write these measures

First date = min('table1'[Date])

Status on first date = calculate(max('Table2'[Status]),datesbetween(Calendar[Date],[First date],[First date]))

Hope this helps.

[FreemanZ]

hi @aallman 

try to add two calculated columns like:

Date =

MINX(

    RELATEDTABLE(Table1),

    Table1[Date]

)

Status =

LOOKUPVALUE(

     Table1[Status],

     Table1[ReferenceID], Table2[ReferenceID],

     Table1[Date], Table2[Date]

)