Forum Discussion

Pragati11's avatar
Pragati11
Icon for Super User rankSuper User
6 years ago
Solved

Grouping data Performance - Query Editor vs DAX

Hello Power BI Experts,   I just wanted to understand the difference in the performance based on column calculations done using DAX versus that done in Query Editor.  Let's consider creating a sum...
Need Help
Show and Tell
Tips and Tricks
  • parry2k's avatar
    parry2k
    6 years ago

    Pragati11 why calculated columns? I use more than 100 million rows of data and with measures, I was able to achieve anything and these were complex measures. First, why you are adding a column? Also, look at aggregation

     

    I personally feel grouping is not a solution in this case.

Pragati11's avatar
Pragati11
Icon for Super User rankSuper User
6 years ago

Hello @parry2k ,

Thank you for your quick response.

My requirement is to create few calucated columns at the client level. Now in my current file I have almost 7 million rows and some other columns related to customer sales.

When I try to use the EARLIER function to write a type of calculations at the client level, the dax formula takes a long time to finish. So, I thought I'd create group-level calculations on an individual customer. But this greatly increases the number of tables in my data model and greatly affects Power BI performance.

Therefore, my question was where it is best to put the group at the client level by calculations.

You may need to think about moving calculations in the database until there is no other way to handle them.

Thank you

Pragati

parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

Pragati11 why calculated columns? I use more than 100 million rows of data and with measures, I was able to achieve anything and these were complex measures. First, why you are adding a column? Also, look at aggregation

 

I personally feel grouping is not a solution in this case.

  • Pragati11's avatar
    Pragati11
    Icon for Super User rankSuper User
    6 years ago

    Hi parry2k ,

     

    May be I am just thinking a lot and started creating columns. Looks like I should start creating measures rather than columns and see how it looks like.

     

    Thanks for this suggestion. I will try this.

     

    Thanks,

    Pragati

    • parry2k's avatar
      parry2k
      Icon for Super User rankSuper User
      6 years ago

      Pragati11 sounds great. Good luck. Yeah, always put MEASURES first and every time you think of creating a column, ask yourself, why you need this? Cheers and good luck. Keep posted and let's know how it goes. 

    • v-lili6-msft's avatar
      v-lili6-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      HI  Pragati11 

      If you need to transform and shape the data model, for example, remove unless columns or get a summary table, etc. you could do it in power query, and this will reduce the size of dataset too.

       

      Then if you want to keep raw data, you need to use DAX to create a calculate column or calculate measure. But I suggest you use a measure instead of column.

      See more details by this blog:

      https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

       

      Regards,

      Lin