The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I am trying to create a caltualated summary that lists each date in my date table, each client and product that that client buys from my Client Products table and the highest single order quantity from my sales table. If the client didn't buy the product that day, list 1.
So in other words, my 3 existing tables are:
Client Products | |
Client | Product |
A | X |
A | Y |
A | Z |
B | X |
B | Y |
C | X |
Dates |
Date |
4/18/2018 |
4/19/2018 |
Sales | |||
Date | Client | Product | Qty Sold |
4/18/2018 | A | X | 2 |
4/18/2018 | A | X | 3 |
4/18/2018 | A | Z | 2 |
4/18/2018 | B | X | 2 |
4/18/2018 | B | Y | 2 |
4/18/2018 | C | X | 2 |
4/19/2018 | A | Z | 3 |
4/19/2018 | A | Z | 4 |
And here's the output i want:
New Table | |||
Date | Client | Product | Largest Order |
4/18/2018 | A | X | 3 |
4/18/2018 | A | Y | 1 |
4/18/2018 | A | Z | 2 |
4/18/2018 | B | X | 2 |
4/18/2018 | B | Y | 2 |
4/18/2018 | C | X | 2 |
4/19/2018 | A | X | 1 |
4/19/2018 | A | Y | 1 |
4/19/2018 | A | Z | 4 |
4/19/2018 | B | X | 1 |
4/19/2018 | B | Y | 1 |
4/19/2018 | C | X | 1 |
Solved! Go to Solution.
Thanks Greg! It didn't get me quite where i needed to be, but close. For reference, the solution is here:
Hi,
You may refer to my solution in this workbook.
Hope this helps.
Thanks Ashish! Unfortunately I couldn't use the Dates table in the data model like you did in Excel because the Dates table was actually a calculated table. I probably should have specified that. For reference the solution is here:
Try this:
New Table = SUMMARIZE(Sales,[Date],[Client],[Product],MAX([Qty Sold]))
BTW, excellently formed question with sample data that can be copied and pasted as well as expected output. Well done.
Thanks Greg! It didn't get me quite where i needed to be, but close. For reference, the solution is here:
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
94 | |
94 | |
86 | |
69 | |
65 |
User | Count |
---|---|
240 | |
125 | |
122 | |
82 | |
80 |