Dear All ,
I have data table with around 10 M rows , I need to do the Market basket analysis process based on this article : https://www.daxpatterns.com/basket-analysis/
The article is very useful and I gained many technical aspects everyone can benefit from it but I had performance issue in the following Measure (# Orders Both (Internal)) :
I read that summarize function gives slowness in the response , as I read from different sources
it is better to use summerizecolumns Instead , I need your advise to edit this measure using (Summerizecolumns) or any other suggestion to improve the performance issue , I attached the pbix file that is available on the
article blog .
Thank you
17 - Basket analysis - Basic.pbix (2.3 MB)
@MAAbdullah47 Did you read the article completely?
@AntrikshSharma Yes , I practices it , I have also used the optimized version (attached with this email) but the same thing happened it is still slow in retrieving.
I did all the steps needed to improve the performance using VertiPaq Analyzer https://www.sqlbi.com/tools/vertipaq-analyzer/ , The File and DB reduced by at least 50% but still the measure retrieve the data slowly .
17 - Basket analysis - Optimized.pbix (2.6 MB)
Hi @AntrikshSharma why you ask me this question?
@MAAbdullah47 The thing with DAX patterns is that they are good for 100K rows but not for Millions, I think SQLBI also makes it explicit that their patterns are not to be used on large datasets.
The problem with the code is it uses TREATAS, EXCEPT, KEEPFILTERS all of these functions are resolved by a slower DAX Engine, so here we have to assume that SQLBI has already done their research and I don’t think it worth spending any time to optimize it any further.
[quote=“AntrikshSharma, post:5, topic:22658”]
The thing with DAX patterns is that they are good for 100K rows but not for Millions, I think SQLBI also makes it explicit that their patterns are not to be used on large datasets.
@AntrikshSharma Is that mean Power bi (DAX) not recommended to be used with large no of data ? what is the alternatives? this fact no one tell anything about it , how they didn’t clarify this fact so everyone can take it into consideration when modeling any dashboard with big amount of data?
That’s not what I said, I said DAX Patterns are not meant to be used with large data models and to be honest without looking at your data I can’t really help.
DAX/Analysis Services is the fastest thing in the market as far I as I know.
Thank you @AntrikshSharma , here is a resources (Data)
https://drive.google.com/file/d/1GAsT46vs0H-he0MBsVqNsTziW62qspb6/view
The schema on the screen-shot , U can Just use the measures available on the Pbix I submitted it on my previous replies , the scenario we my use it , Simple table (Not matrix) , Top 10 Combination of measure (Orders Both products) for Product Key (And product table) with Key in (Products table) similar to the one on Daxpattern , appreciated your help.
I hope and looking for your kind response.
Note: For the data is masked in Products table (Product 1,2, …X) and any one can benefit from it because it represent a real data but (Masked).
Bumping this post for more visibility.
Hi @MAAbdullah47! We noticed that your inquiry was left unsolved for quite some time now.
Looks like your inquiry was out of the experts and users’ bounds.
We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.
While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!
