Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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 🙂
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |