Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Category Combination Purchase

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 s...
Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    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's avatar
v-angzheng-msft
Icon for Community Support rankCommunity Support
4 years ago

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's avatar
Anonymous
Not applicable
4 years ago

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.