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

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

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.

7 Replies

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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 

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    4 years ago

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

      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's avatar
    v-angzheng-msft
    Community Support
    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.