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.
Hello,
I'm trying to categorize my products based on Sales and Cost conditions.
Based on the majority of the products category, we are then categorizing the manufacturer.
This is my product category condition:
ProductCategory =IF(AND(C2>=200, D2<=5), "CAT1", IF(AND(C2>=100, D2<=10), "CAT2", IF(AND(C2>=50, D2<=20), "CAT3", "CAT4")))
Following an Excel simulation, this is my manufacturer category condition:
=COUNTIF($E$2:$E$7,G2)
So for example for Manufacturer A, most of the products are categorized as CAT3 so manufacturer A is labeled CAT3.
I want to achieve the same in Power BI where the manufacturer category is a measure that returns one value for each manufacturer based on the majrity of the products categories.
Up until product category things are quite straight foward.
When categorizing the manufacturer, this is were things get messy.
I am not able to formulate a measure that does this calculation in a neat and fast way.
Knowing that my dataset counts more than 10 million records and increases every day, the filters on the pages are not a lot so the measure does exceed the limits.
Any help on how to write the measure effectively?
Thanks,
Sabine O.
Hi @SabineOussi ,
Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.
Best Regards
Lucien
@SabineOussi no, star schema will improve the performance, right now it is scanning 1.7 m rows for each manufacturer, if we have a separate manufacturer dimension, it will be small and the scan time will be way little. These are the basics of data modeling and best practices for power bi.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@parry2k You're absolutely right about that, my whole model is actually based on a star schema.
For some reason I was thinking of something else.
In all cases, I tried the measure on the manufacturer from the other table. Results are consistent however performance is still the same.
Any other suggestion?
Thanks,
Sabine O.
@SabineOussi not surprised, do you have a separate table for the manufacturer, if not, we need to create one and that will improve the performance.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@parry2kHmm, wouldn't a separate table slow the performance?
So I will have the main product table with an ID to the manufacturer and the manufacturer info in another table and they will be linked with ID?
I'll give it a try and let you know.
Thanks again,
Sabine O.
@SabineOussi add a new column for product category and then a measure and that will do it
Product Category Column =
VAR __cost = 'Table'[Cost]
VAR __sales = 'Table'[Sales]
RETURN
SWITCH ( TRUE(),
__sales >= 200 && __cost <= 5, "CAT1",
__sales >= 100 && __cost <= 10 , "CAT2",
__sales >= 50 && __cost <= 20, "CAT3",
"CAT4"
)
Manufacturer Category =
VAR __m = MAX ( 'Table'[Manufacturer] )
VAR __table =
SUMMARIZE (
FILTER(
ALL ('Table' ),
'Table'[Manufacturer] = __m
),
'Table'[Manufacturer],
[Product Category] ,
"@Cnt", COUNTROWS ('Table' ) )
RETURN
MINX ( TOPN ( 1, __table, [@Cnt], DESC ), [Product Category] )
Check my latest video on Filters and Sparklines https://youtu.be/wmwcX8HvNxc
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thanks @parry2k
The measure serves the purpose and results are well aligned.
However when choosing a selection of more than one million records, the visual exceeds the number of available resources. My dataset has ~17M rows and expected to grow double by the end of the year.
Any thoughts?
Thanks,
Sabine O.
@SabineOussi sure. can you paste your data as a table.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@parry2k Here you go
Manufacturer | Product | Sales | Cost |
A | A.1 | 100 | 5 |
A | A.2 | 50 | 10 |
A | A.3 | 30 | 5 |
A | A.4 | 20 | 15 |
A | A.5 | 50 | 20 |
A | A.6 | 80 | 5 |
A | A.7 | 10 | 30 |
B | B.1 | 150 | 15 |
B | B.2 | 200 | 5 |
B | B.3 | 80 | 10 |
B | B.4 | 60 | 10 |
B | B.5 | 40 | 15 |
C | C.1 | 50 | 10 |
C | C.2 | 450 | 45 |
C | C.3 | 20 | 40 |
C | C.4 | 30 | 10 |
Thanks,
Sabine O.
@SabineOussi what happens if there is a tie between categories?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi @parry2k
I'm guessing by default it will take FIRST category result and I don't mind that.
Thanks,
Sabine O.
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |