The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hello Community
I have a table of receipts containing: date, receipt ID, product ID, amount, and quantity of goods sold.
One receipt can contain several items, for example.
Receipt with identification number M00012 contains the following goods: identification numbers 21, 22, 25, 26. Such
There can be many such receipts, and they may contain identical items.
In the visual matrix, I display the receipt ID, amount, and quantity.
Now I sort by amount and see which product was purchased the most, and now my task in another visualization is to see which products were related in those receipts where the product I highlighted in the matrix table was.
PBIX file example
Solved! Go to Solution.
You need to make some changes to the model. You need 2 copies of a product table, which is just the distinct product IDs. You'll use one in the matrix and the other in the slicer. Create a relationship from the one for the matrix to the sales table, leave the slicer version disconnected from everything.
You can then create a calculation group to apply the same related receipts logic to any measure, with a calculation item like
Show Related Receipts =
VAR Receipts = CALCULATETABLE(
VALUES( 'sales table'[Receipt ID] ),
TREATAS( VALUES( 'Product for Slicer'[Product ID] ), 'sales table'[Product ID] ),
REMOVEFILTERS( 'Product' )
)
VAR Result = CALCULATE( SELECTEDMEASURE(), KEEPFILTERS( Receipts ) )
RETURN Result
Apply this calculation item as a filter to the matrix and that will then show all products which appear in receipts which also contain the product you select in the slicer.
See the attached PBIX for reference.
You need to make some changes to the model. You need 2 copies of a product table, which is just the distinct product IDs. You'll use one in the matrix and the other in the slicer. Create a relationship from the one for the matrix to the sales table, leave the slicer version disconnected from everything.
You can then create a calculation group to apply the same related receipts logic to any measure, with a calculation item like
Show Related Receipts =
VAR Receipts = CALCULATETABLE(
VALUES( 'sales table'[Receipt ID] ),
TREATAS( VALUES( 'Product for Slicer'[Product ID] ), 'sales table'[Product ID] ),
REMOVEFILTERS( 'Product' )
)
VAR Result = CALCULATE( SELECTEDMEASURE(), KEEPFILTERS( Receipts ) )
RETURN Result
Apply this calculation item as a filter to the matrix and that will then show all products which appear in receipts which also contain the product you select in the slicer.
See the attached PBIX for reference.
@johnt75 Hi, I am very grateful for this decision; it is the result I was hoping for.
Hi @DimaMD
In your example file you have the product not the receiptID in the matrix.
Add the ReceiptID into the Matrix and it should work as you describe:
If you are happy with this answer please mark as a solution for others to find !
Kudos are always appreciated! Check out our free Power BI video courses.
Hi, @SamWiseOwl
Thank you for your reply, but the logic is to see all the goods that were sold during the selected period. Sorry, I specified the order incorrectly. The first table shows the product ID, amount, and quantity, and when I click on any product in the other table, it should display all the products that were in all receipts where this product was sold.
User | Count |
---|---|
16 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
26 | |
13 | |
12 | |
8 | |
8 |