topic Re: Creating measure to give first result from related tables in DAX Commands and Tips

Creating measure to give first result from related tables

jessifi — Thu, 14 May 2020 13:07:41 GMT

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

Re: Creating measure to give first result from related tables

nandukrishnavs — Thu, 14 May 2020 15:02:44 GMT

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 🙂

Re: Creating measure to give first result from related tables

jessifi — Mon, 01 Jun 2020 23:53:40 GMT

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.

Re: Creating measure to give first result from related tables

mahoneypat — Tue, 02 Jun 2020 01:43:34 GMT

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