Creating measure to give first result from related tables

[jessifi]

I have two tables

Quarter 1 table- A list of sales associated with a Customer ID. Each sale indicates the product purchased and gives a customer rating for that product.

Customer ID	Product Code	Customer rating
0001	Bike # 7	A
0002	Bell #1	B
0003	Bike # 5	B

Product table - A list of unique products and the category that they are associated with.  There are seven categories.

Product	Category
Bike # 7	Bike
Bike # 5	Bike
Bell #1	Bell

The two tables are related through a one to many relationship from Product to ProductCode.

I want to create a measure "Bike Category Rating"  that for each customer ID will return the first customer rating in the list based off of the product category "Bike" and will provide a blank response if there is no bike category rating

Eg  would look as below in a table

Customer ID	Bike category rating	Bell Category rating
0001	A
0002		B
0003	B

Thanks

[mahoneypat]

I don't see a date column, so I assume there is a single value only for any customer/category combination in your actual model.  If so, this should work:

Make a matrix visual with Customer ID column on the rows, and Category column from the Product table.  Then put a measure like this in the values area - Rating = Min(Quarter[Customer rating])

Since you have a single value for each combo, this should work (Max would work same).  You could also just drag the rating column in the values area and choose 'First' aggregation.

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[nandukrishnavs]

@jessifi 

You could do this using EditQuery.

Create a blank query and copy-paste below query. Then go to steps one by one and understand it.

Product table 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLlHSUXJOLElNzy+qVIrViVZyysxOVVBWMAeKg5jIYqYoYqk5OQrKhiAhIEspNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Product", type text}, {"Category", type text}})
in
    #"Changed Type1"

Quarter 1 table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tLsnPTS1S8HRR0lEKKMpPKU0uUXDOT0kFcuGSRYklmXnpSrE60UoGBgaGQCmnzOxUBWUFcyDTESZuBBJPzclRUAargAkbI5SbQsRjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Merged Queries" = Table.NestedJoin(#"Promoted Headers", {"Product Code"}, #"Product table", {"Product"}, "Product table", JoinKind.LeftOuter),
    #"Expanded Product table" = Table.ExpandTableColumn(#"Merged Queries", "Product table", {"Category"}, {"Product table.Category"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Product table",{"Product Code"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"Product table.Category"]), "Product table.Category", "Customer rating")
in
    #"Pivoted Column"

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[jessifi]

Hi nandukrishnavs,

I couldn't get your solution to work,  I think i'm just a little bit too inexperienced but I manged to solve my problem by using an IF statment to create a new column.