Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Category Combination Purchase

I have sample data as shown below


CustomerId BillId Month Item            Sales

abc1Janchicken100
abc2JanMutton100
xyz3JanEggs50
xyz3Janchicken100
sab4JanMutton400
frq5JanEggs200
pol6Janchicken200
pol6JanMutton300
pol6JanEggs50


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

1 ACCEPTED SOLUTION
v-angzheng-msft
Community Support
Community Support

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.

View solution in original post

7 REPLIES 7
v-angzheng-msft
Community Support
Community Support

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
Community Support
Community Support

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:

vangzhengmsft_0-1642479188864.png

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
Not applicable

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

Capture.PNG

Please help me as i am very close
Thanks

v-angzheng-msft
Community Support
Community Support

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:

vangzhengmsft_0-1642389261347.png

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
Not applicable

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

abc1Janchicken100
abc2JanMutton100
xyz3JanEggs50
xyz3Janchicken100
sab4JanMutton400
frq5JanEggs200
pol6Janchicken200
pol6JanMutton300
pol6JanEggs50
sat7Janchicken250
sat8JanMutton250
bat9JanEggs490
abc10Febchicken240
abc11FebEggs50

 

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
Not applicable

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
Super User
Super User

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.