Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
NipponSahore
Resolver II
Resolver II

Matrix - Show all rows, irrespective of slicer

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 1ProductA1ASeller1
Customer 2ProductC1CSeller2
Customer 3ProductB2BSeller3
Customer 4ProductA1ASeller4
Customer 5ProductA1ASeller1
Customer 6ProductC1CSeller2
Customer 7ProductA2ASeller3
Customer 8ProductC2CSeller4
Customer 9ProductB1BSeller1
Customer 10ProductA2ASeller2
Customer 1ProductB1BSeller3
Customer 2ProductA2ASeller4
Customer 3ProductB1BSeller1
Customer 4ProductB2BSeller2
Customer 5ProductA1ASeller3
Customer 6ProductC1CSeller4
Customer 7ProductA2ASeller1
Customer 8ProductB1BSeller2
Customer 9ProductA2ASeller3
Customer 10ProductB1BSeller4

 

My requirement is the following:

 

 

  1. Create 2 slicers on 
    1. Seller(this will remain fixed most of the time,so we dont need to worry about this)
    2. Product Type
  2. Create a matrix with the following 
    1. Rows are all Customers for this seller, irrespective if they are sold a product in this product type
    2. Columns are Products
    3. Values are 1 and 0 if they are being sold or not

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 ProductA1ProductA2
Customer 11 
Customer 3  
Customer 51 
Customer 7 1
Customer 9  

 

And if i select Seller = Seller1 and Type = B the result should be 

 

Customer ProductB1
Customer 1 
Customer 31
Customer 5 
Customer 7 
Customer 91

 

Also on another note, my dataset > 22M records, so i cant pivot this data. Anything on DAX would be great

6 REPLIES 6
pnvinod
Helper I
Helper I

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 🙂 

quentin_vigne
Solution Sage
Solution Sage

Hi @NipponSahore

 

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

Can you please elaborate on this?

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.