Category Combination Purchase

[Anonymous]

I have sample data as shown below

CustomerId BillId Month Item            Sales

abc	1	Jan	chicken	100
abc	2	Jan	Mutton	100
xyz	3	Jan	Eggs	50
xyz	3	Jan	chicken	100
sab	4	Jan	Mutton	400
frq	5	Jan	Eggs	200
pol	6	Jan	chicken	200
pol	6	Jan	Mutton	300
pol	6	Jan	Eggs	50

I need the desired output as

only Chicken - 0 customer
only Eggs - 1 customer
only Mutton - 1 customer
Chicken-Eggs - 1 customer
Chicken-Mutton - 1customer
Egg-Mutton - 0 customer
chicken-Mutton-Eggs - 1customer

Please help me how should I create the logic for this?

As I am new to power bi
Thanks

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

I don't think the month-wise slicer has a significant impact on the timing of generating the results. The time is too long mainly because you have too much data, I guess the connection mode of your model data is import? Changing the data connection mode to direct query or live connection may help.

There is a month-wise slicer that provides additional calculation logic. If you don't need it, just delete the judgment statement about the month-wise slicer in the above measures.
Like:

_I_Item = 
var _I_Item=SUMMARIZE(FILTER(ALL('Table'),'Table'[CustomerId]=MAX('Table'[CustomerId])),[Item ])
var _I_Item_Month=SUMMARIZE(FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])&&'Table'[CustomerId]=MAX('Table'[CustomerId])),[Item ])

var _if_Month=
    CONCATENATEX(_I_Item_Month,[Item ],","),CONCATENATEX(_I_Item,[Item ],",")
return _if_Month

Is the above post helpful to you? If  it does, could you please mark the post which help as Answered? It will help the others in the community find the solution easily if they face the same problem with you. Thank you.😀

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-angzheng-msft]

Hi, @Anonymous 
Create another calculation table to determine if it is month-wise.

Month =
DATATABLE( "Month-Wise", STRING, { { "Yes" }, { "No" } } )

Modify the above measures as follows:

_I_Item = 
var _I_Item=SUMMARIZE(FILTER(ALL('Table'),'Table'[CustomerId]=MAX('Table'[CustomerId])),[Item ])
var _I_Item_Month=SUMMARIZE(FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])&&'Table'[CustomerId]=MAX('Table'[CustomerId])),[Item ])
var _if_Month=
    IF(ISFILTERED('Month'[Month-Wise])&&SELECTEDVALUE('Month'[Month-Wise])="Yes"
        ,CONCATENATEX(_I_Item_Month,[Item ],","),CONCATENATEX(_I_Item,[Item ],","))

return _if_Month

_isOnly = 
var _M_Item=SUMMARIZE(ALLSELECTED('ID-Item'),[Item ])
var _I_Item=SUMMARIZE(FILTER(ALL('Table'),'Table'[CustomerId]=MAX('Table'[CustomerId])),[Item ])
var _I_Item_Month=SUMMARIZE(FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])&&'Table'[CustomerId]=MAX('Table'[CustomerId])),[Item ])

var _except_L=IF(ISFILTERED('Month'[Month-Wise])&&SELECTEDVALUE('Month'[Month-Wise])="Yes",COUNTROWS(EXCEPT(_M_Item,_I_Item_Month)),COUNTROWS(EXCEPT(_M_Item,_I_Item)))
var _except_R=IF(ISFILTERED('Month'[Month-Wise])&&SELECTEDVALUE('Month'[Month-Wise])="Yes",COUNTROWS(EXCEPT(_I_Item_Month,_M_Item)),COUNTROWS(EXCEPT(_I_Item,_M_Item)))
var _isOnly=IF(_except_L=0&&_except_R=0,1,0)


return _isOnly

Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Dear @v-angzheng-msft 
Your Logic is working fine. But because of the month-wise Table, my data is taking a very long time(approx half-day)  to get results. I am having more than 20lacs of data.
can you suggest another way that we dont have to put month-wise slicer.
 
I need the solution as per below sample image

Please help me as i am very close
Thanks

[v-angzheng-msft]

Hi, @Anonymous 

Create a summary table as follows:

ID-Item = SUMMARIZE(ALL('Table'),[CustomerId],[BillId],[Item ])

Create the following measures:

_isOnly = 
var _M_Item=SUMMARIZE(ALLSELECTED('ID-Item'),[Item ])
var _I_Item=SUMMARIZE(FILTER(ALL('Table'),'Table'[CustomerId]=MAX('Table'[CustomerId])),[Item ])
var _except_L=COUNTROWS(EXCEPT(_M_Item,_I_Item))
var _except_R=COUNTROWS(EXCEPT(_I_Item,_M_Item))
var _isOnly=IF(_except_L=0&&_except_R=0,1,0)
return _isOnly

_getID = 
var _getID=SUMMARIZE(FILTER('Table',[_isOnly]=1),'Table'[CustomerId])
return CONCATENATEX(_getID,[CustomerId],",")

_countID = 
var _getID=SUMMARIZE(FILTER('Table',[_isOnly]=1),'Table'[CustomerId])
return COUNTAX(_getID,[CustomerId])

Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Dear @v-angzheng-msft 
Your Solution is working fine.
But if the customer is purchasing next month then I am not able to get the count month-wise.
For example. customer 'abc' has also purchased in Feb as shown in the below data.

CustomerIdBillIdMonthItem Sales

abc	1	Jan	chicken	100
abc	2	Jan	Mutton	100
xyz	3	Jan	Eggs	50
xyz	3	Jan	chicken	100
sab	4	Jan	Mutton	400
frq	5	Jan	Eggs	200
pol	6	Jan	chicken	200
pol	6	Jan	Mutton	300
pol	6	Jan	Eggs	50
sat	7	Jan	chicken	250
sat	8	Jan	Mutton	250
bat	9	Jan	Eggs	490
abc	10	Feb	chicken	240
abc	11	Feb	Eggs	50

now 'abc' has purchased chicken and Mutton in Jan and
'abc' has purchase chicken and Egg in Feb

I should get a count as
Chicken, Mutton - 1count for abc Jan
chicken, Eggs - 1count for abc Feb

I am getting
Chicken,Mutton,Eggs - 1count 'abc'

I need month-wise
please look into this 

Thanks for your help it's appreciated.

[Anonymous]

The link you shared, I have gone through it. but my scenario is different.
Please see what I need the answer. It's different from the market basket 

[amitchandak]

@Anonymous , to me it seem more like Market Basket Analysis
https://finance-bi.com/power-bi-basket-analysis/
https://businessintelligist.com/2017/03/27/market-basket-analysis-using-power-bi/