First value within a dimension of a pivot table

[richard_chilvers]

I have the following table (example)

PRODUCT BOOKING_REF SURNAME

Prod 1         Ref100               Smith

Prod 1         Ref100               Simpson

Prod 1         Ref101               Jones

Prod 1         Ref101               James

Prod 2         Ref102               Simons

etc.....

I need a pivot table where PRODUCT and BOOKING_REF are dimensions, and showing just the first SURNAME for each BOOKING_REFon each row. So:

Prod 1     Ref100     Smith

Prod 1     Ref101     Jones

Prod 2     Ref102     Simons

I kind of expected the FIRST function to work as my expression, but I just get NULL values. Struggling to understand the documented definition of this function. Any help or solutions ?

Thanks

[JonnyPoole]

I added a key to your load called recordnumber. This allows me to number each line of data 1,2,3...

Then i used firstsortedvalue()  to pull the surname with the lowest recordnumber for each combination of the other 2 dimensions.

Attached is full sample