Hello,
I have a sales table, with multiple items in every receipt. I want to filter with a slicer the receipts that have a specific item (product), but get the whole basket for these receipts and not only the specific item.
Tried the following measure as a filter on the specific visual, but doesn’t seem to work:
Filtered receipt ID =
IF(MAX(Sales[Receipt ID]) IN CALCULATETABLE(
VALUES(Sales[Receipt ID]),FILTER(
SALES, Sales[Product ID] = SELECTEDVALUE(Products[Product ID])))
,1,0)
Each receipt has multiple items (Sales ID) and each Sales ID corresponds to Product ID. Slicer is on Product ID
Thanks in advance for helping out if possible!
Zissis
Hello again,
I am uploading a sample pbix.
The desired outcome is that when I filter for Product X that is in the Receipts Y,Z,E,etc. , I want to get all the products that are in the receipts Y,Z,E and not just the lines with Product X.
Same logic for other filtering parameters that I have in the model, like brand or category.sales_demo_oct19.pbix (511.2 KB)
Thanks again for taking the time and help!
Zissis
You could try to solve this by adding a slicer with Receipt ID and editting the interactions between the slicers. In this case there should be no interaction between the slicer with Product ID and a table or matrix with the products and receipts. The interaction between the Receipt ID and Product ID should be active. I have added 2 printscreens to show you what I mean.
I hope this helps. I am sure there will be better solutions, but this is the only one I could think of.
Daniel
Hello Zissis,
You could try to solve this by adding a slicer with Receipt ID and editting the interactions between the slicers. In this case there should be no interaction between the slicer with Product ID and a table or matrix with the products and receipts. The interaction between the Receipt ID and Product ID should be active. I have added 2 printscreens to show you what I mean.
I hope this helps. I am sure there will be better solutions, but this is the only one I could think of.
Daniel thanks for the proposal. Indeed it works for unique receipts.
My issue is that I want all the receipts that contain the specific item, with all the items those receipts contain, in order to perform basket analysis.
I’ve had a look at the model and I’m just not getting it 100% what you are looking for. This is a complex request so it can be confusing sometimes.
Can you lay out an example of exactly what you are looking in images based on the model you have sent. Then maybe also add another model with what you’ve started on regarding the formula etc.
Hello Sam,
Sure, check out the below - I have changed some field names in order to be more clear. So, I have multiple Receipts, which have multiple Receipt Items included. Each Receipt Item has Brand and Category. When I filter on Brand ‘6’, I get the following records:
Naturally I get only the records that have Brand ‘6’.
What I would like to get is the Receipt records that have at leaste one Receipt Item of Brand ‘6’, but with ALL of their Receipt items included. So something like the below
I’ll have a good look at this again tomorrow as there’s a bit to this one.
One thing I’m confident about is that you will not want an active relationship in your model. The active relationship causes the natural filtering and there’s no way to really work around this with formula. So another approach with either and an inactive relationship or no relationship at all would likely need to be worked through
@zissis.ts
I took a look at this and I think this should provide some good guidance. I did this all in DaxStudio because I wanted to see the actual tables, but you can modify into measures since you would not actually want to materialize these tables.
Anyhow, here’s a screenshot with all my notes and I attached the DAX query as well.
