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
Hi,
I am looking for some help with building 2 new columns in my dataset. They are:
Example:
For desired output 1 i need to find all of the products that are related to each other. See colour coded groups above. I have used the folloing formula but it doesnt give the right result.
Desired Output 1 = CALCULATE(MINX(testdata,testdata[Group 1]),FILTER(testdata,testdata[Group 2] = EARLIER(testdata[Group 2])))
In SQL we use a CASE statement with PARTION BY to generate the desired output. We would like to use Power BI however as it offers more flexibility.
It may be that a measure rather than column is a better alternative. Open to suggestions and would like some help please
Thanks
Aaron
Can you explain what the logic is behind DesiredOutput1? Is it - if both values in group1 and group2 haven't occured yet in the table, then up the index by 1?
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
Thanks for trying to clarify the question. I still don't fully understand the logic of grouping the products together based on Group1 and Group2. I understand that the numbers in both columns are reflecting categories from the same list of categories?
Is there a hard mapping between group1 and desiredOutput1? For example: you want to group products from Group1=1 && Group1=2 together, and you want to group products from Group1=emtpy together, and you want to group products from Group1=8 together? If that is hardcoded then you can create a calculated column with Switch:
DesiredOutput1 = SWITCH([Group1], 1, 1,
2, 1,
"", 2,
8, 3)
Your desiredoutput2 can then be:
DesiredOutout2 =
VAR currentGroup = Table1[DesiredOutput1]
RETURN
CALCULATE(MIN(Table1[New Price]), FILTER(Table1, Table1[DesiredOutput1] = currentGroup))
Hope this helps you out? Let me know! 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
Thanks for the response. Yes the products are in the same product category.
There are too many groups (1 & 2) to hard code the result (200+ in each). Plus, the products in the groups change weekly so think the solution needs to be dynamic i.e. cycle through all of the combinations in 1 & 2 to build the desired output.
I've updated the example to help provide some more context
Product Id | Product Detail | Group 1 (Same Size & Different Flavour) | Group 2 (Different Size & Same Flavour) | Combination | Desired Out Put1 (Super Group) |
4 | Small Crisps - Plain | 1 | 1, | 1 | |
7 | Small Crisps - BBQ | 1 | 3 | 1,3 | 1 |
17 | Large Crisps - BBQ | 2 | 3 | 2,3 | 1 |
12 | Small Crisps - Chilli | 1 | 4 | 1,4 | 1 |
13 | Large Crisps - Chilli | 2 | 4 | 2,4 | 1 |
11 | Small Crisps - Cheese | 1 | 5 | 1,5 | 1 |
14 | Large Crisps - Cheese | 2 | 5 | 2,5 | 1 |
9 | Small Crisps - Salted | 1 | 6 | 1,6 | 1 |
15 | Large Crisps - Salted | 2 | 6 | 2,6 | 1 |
16 | Large Crisps - Tangy | 2 | 2, | 1 | |
18 | Yoghurt | - | - | -,- | 2 |
24 | Milk Full Fat | 8 | - | 8,- | 3 |
20 | Mulik Semi Skimmed | 8 | - | 8,- | 3 |
21 | Carrot | - | - | -,- | 4 |
200 | Burger | - | - | -,- | 98 |
250 | Cheese | - | - | -,- | 100 |
For the crisps group. I have re-sorted the example to better show how the products relate to each other in terms of product size and flavour. Where there isnt different sizes of a flavour i.e. Small plain and Large tangy, these are still part of the desired output as they are part of Grouping 1 where other flavours are bound to group 2 through different sizes
I hope this makes sense
I forgot to say i should have made it clear that my example was just an exmple rather than the total dataset. Apologies!
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 |
---|---|
105 | |
97 | |
81 | |
62 | |
54 |
User | Count |
---|---|
246 | |
119 | |
114 | |
87 | |
70 |