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