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.
Hi Team,
I have a following issue, which is giving me a hard time.
Here is the scenrio.
I have a dataset with customer, seller, product and product type data.
Here is an example
Customer | Product | Product Type | Seller |
Customer 1 | ProductA1 | A | Seller1 |
Customer 2 | ProductC1 | C | Seller2 |
Customer 3 | ProductB2 | B | Seller3 |
Customer 4 | ProductA1 | A | Seller4 |
Customer 5 | ProductA1 | A | Seller1 |
Customer 6 | ProductC1 | C | Seller2 |
Customer 7 | ProductA2 | A | Seller3 |
Customer 8 | ProductC2 | C | Seller4 |
Customer 9 | ProductB1 | B | Seller1 |
Customer 10 | ProductA2 | A | Seller2 |
Customer 1 | ProductB1 | B | Seller3 |
Customer 2 | ProductA2 | A | Seller4 |
Customer 3 | ProductB1 | B | Seller1 |
Customer 4 | ProductB2 | B | Seller2 |
Customer 5 | ProductA1 | A | Seller3 |
Customer 6 | ProductC1 | C | Seller4 |
Customer 7 | ProductA2 | A | Seller1 |
Customer 8 | ProductB1 | B | Seller2 |
Customer 9 | ProductA2 | A | Seller3 |
Customer 10 | ProductB1 | B | Seller4 |
My requirement is the following:
My problem lies in not being able to show a 0 for products that are not sold by seller. As there is no record in the underlying dataset, if i try to asign a default value, it shows me all the customers even if they are not being catered to by the seller.
As an example. if i select seller = Seller1 and select Product Type = A the result should be
Customer | ProductA1 | ProductA2 |
Customer 1 | 1 | |
Customer 3 | ||
Customer 5 | 1 | |
Customer 7 | 1 | |
Customer 9 |
And if i select Seller = Seller1 and Type = B the result should be
Customer | ProductB1 |
Customer 1 | |
Customer 3 | 1 |
Customer 5 | |
Customer 7 | |
Customer 9 | 1 |
Also on another note, my dataset > 22M records, so i cant pivot this data. Anything on DAX would be great
Create a new Table "Products" with distinct products from the first table.
Add a cartisian joint between the 2 tables.
Add a related Product in the original Table.
Add a Custom Column that returns 1 when Product = related Product else 0
In the matrix use this custom column in Values.
Currently working with 22M records, So prefer a DAX solution.
Any other suggestions 🙂
If your only problem is not being able to show a 0 instead of blank, you just have to simply add ' + 0 ' to your column.
It will display 0 instead of blank.
- Quentin
I'm trying to solve your problem
It's tricky, I almost display the expected result but I also display the value that customer bought from other sellers
- Quentin
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 |
---|---|
108 | |
98 | |
81 | |
65 | |
62 |
User | Count |
---|---|
147 | |
116 | |
104 | |
88 | |
65 |