Hi Team,
Request your help regarding customer buying pattern. I am attaching an Excel Data model (with PowerPivot) where I have sales data, product master and criteria table. Basically I would like to find out list of customers who have bought “ABC” product but not “DEF” product. I need to put my slicer from “Criteria” table so that I can share actionable insights with my sales team. I have gone thorough many other sources and videos but I couldn’t find the solution.
Please help me in this regards. You may change or content in any table.
Created two disconnected tables composed the unique brand names and associated slicers with each.
Standard harvest measure for the first slicer provided Brand Bought, while the second provided Brand Not Bought.
Created the following measure, which creates two virtual table variables - the first captures companies who bought the brand harvested from the first slicer, the second captures companies who bought the brand harvested from the second slicer, and then filters the company list down to those companies that appear in the first virtual table and don’t appear in the second.
Output the results to a scalar that looks like a table, using CONCATENATEX and UNICHAR(10) (the code for hard return)
Used a one-cell table with title for the visual, which provides better formatting control than a card.
Here’s the measure that does the heavy lifting:
Bought and Not Bought =
VAR _Bought1 =
CALCULATETABLE(
VALUES( Customers[Company] ),
FILTER( ALL( Data ), Data[Brand Name] = [Harvest Bought] )
)
VAR _Bought2 =
CALCULATETABLE(
VALUES( Customers[Company] ),
FILTER( ALL( Data ), Data[Brand Name] = [Harvest Not Bought] )
)
VAR _FirstNotSecond =
CALCULATETABLE(
VALUES( 'Customers'[Company] ),
FILTER(
'Customers',
'Customers'[Company]
IN _Bought1
&& NOT ( Customers[Company] IN _Bought2 )
)
)
VAR _Result =
CONCATENATEX(
_FirstNotSecond,
Customers[Company],
UNICHAR( 10 ),
Customers[Company], ASC
)
RETURN
_Result
And here’s what it looks like all put together:
I hope this is helpful. Full solution file provided below.
Thanks a lot @BrianJ for your prompt response as always. I have one issue though. In my practical data (my organization’s data) I have long list of brands where my key key filter is “Segment”. When I am putting “Segment” as slicer then nothing is happening in “Brand Name Bought” and “Brand Name Not Bought”. Where as it should also get filtered while selecting segment in slicer. Please find attached screen shot.
The reason nothing happens when you pull Segment in as a slicer is that the other slicers come from disconnected tables, therefore there’s no relationship/syncing that occurs between your segment slicer and the disconnected brand slicers. To make that work, you need to expand the disconnected tables to include segment, which can be done easily by changing VALUES to ALL in the DAX expressions that create the disconnected tables:
Hi @BrianJ . I am finding one difficulty though, while everything is working fine but after selection of slicers, Volume figure is not correct in the table for selected slicer. Pls find attached screenshot and help me in this regard. Volume should be selected segment and selected brand (for those who have bought products)
Can you please do me a favor? I am swamped for the next couple of days with a number of short deadline projects, but don’t want to lose track of this one. Can you please open a new thread with your question and provide a mockup of the specific visual you want to see with correct numbers, so I can validate my solution against that?
