I've been trying to figure out this issus for weeks but I seem to hit deadend all the time and hoping to get some help here.
I know that a disconnect table is needed but I just can't seem to make it work with:
Selectedvalue and Switch
1- All the Product ID
2- Current Sales based on date and product id
I've written a measure to indicate of the 45 Products id, which are "Puchased' and "Not Purchased" by a respective store. And have the visual as a Table.
User can click on a silcer: "Purchased" and "Not Purchase".
The table will filter accordingly and display the product id that are either "Purchased' and "Not Purchase", based on the selection.
If no selection i done on the silcer, show all.
Cannot create a slicer from a measure.
Best you can do is write a Power Query formula for "Puchased' and "Not Purchased" and then put it in a slicer in the front end.
I hope it makes sense.
I know is measure is not possible as a slicer thus using a disconnected table might help.
I read it from the various blogs but the use cases are for calculation purposes.
PowerQuery might not work for me as I've quite a big data set and I've to pick based on store location.
As my files are quite huge, writing those extra logic might slow down the data model.
Writing a measure helps to reflect the status but not allow me to filter it.
I can get the user to sort it but that not a long-term solution.
If I understand your scenario correctly that you want to create a slicer to allow users click "Purchase" and “Unpurchase”.
What about creating the calculated column with the formula below?
Column = IF(CALCULATE(SUM(Table1[Sales]))>0,"purchase","unpurchase")
You could have a reference of the attachement.
If you still need help, please share a share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)
@v-piga-msft, Thanks, let me look at it first and get back to you when i'm back in the office tomorrow
Is not possible to do calculated column since "Purchase" & "unpurchase" is determined by Sales_File thus only "Purchase" can be created by calculated column.
There is a master product list file that have all the products and that determine if a customer has "Purchase" or didn't purchase the product.
Let me look at my data model and masked the confidential details. I prob post it over the weekend
Have you solved your problem?
If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If you still need help, please share your data sample and your desired output so that we could help further on it.
@v-piga-msft, sorry for the late reply. Nope, the earlier solution didn't help with my case.
I'm preparing a sample data so i can send it over by the end of the week but is there a email i can send so i can send the original copy?
Sorry for the delay
Super sorry for the delay. Here is the link
The scenario is, I've to create multiple year, side by side against the products (Like in the example)
And the "Purchase/UnPurchase" is refering to 2013, as that is our main focus for the existing year.
We need to create a filter for "Purchase/Unpurchase" for 2013 so user can quickly glance through or export the data which is being narrowed based on Purchase or Unpurchase
Hope that help explain my situation
Why don't you try creating a Visual level filter (EnableSlicer = 1) on the Table based on the below measure:
EnableSlicer = VAR SlicerValue = SELECTEDVALUE ( 'Helper Slicer'[Purchased|NotPurchase] ) VAR PurchaseFlag = [P|NP] RETURN IF ( PurchaseFlag = SlicerValue || ISBLANK ( SlicerValue ), 1, 0 )
Assuming you already have [P|NP] measure added as a column in the table, and the values of [P|NP] is in sync with Disconnected slicer values, everything should work fine.
thanks for the input. I tried your methods but i cant seem to get it to filter to just "Purchase" or "Not Purchase".
Not too sure what went wrong on this. I created a calculated column with 1 & 0 as input to match with the Disconnect table but it doesnt seem to work thou
I put the latest file with the edits in.
appreciate any help here