none
summarize vs groupby RRS feed

  • Question

  • Hi,
    How can the below DAX use groupby instead of summarize please?

     SUMMARIZE (
            'TablePH',
            'TableP'[Field1],
            'TableP'[Field3],
            "CountOfCountries", DISTINCTCOUNT ( 'Table6'[Country] ),
            "Hours", [Time]
        )
    Friday, September 20, 2019 4:04 PM

Answers

  • Hi

    Could you try once with the below.

    CALCULATE(DISTINCTCOUNT('Table6'[Country]), GROUPBY('TablePH','TableP'[Field1],'TableP'[Field3],"Hours", [Time]))

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    • Marked as answer by arkiboys Wednesday, September 25, 2019 3:57 PM
    Wednesday, September 25, 2019 2:48 PM

All replies

  • Firstly, summarise in DAX is essentially group by in SQL Second, this is more efficient Addcolumns(SUMMARIZE ( 'TablePH', 'TableP'[Field1], 'TableP'[Field3]) "CountOfCountries", calculate(DISTINCTCOUNT ( 'Table6'[Country] )), "Hours", [Time] ) Assuming [time] is a measure

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au


    Saturday, September 21, 2019 6:02 AM
    Answerer
  • Hi Matt,
    I read your book on power BI. very good

    I don't think you answered my question because I would like to know how to convert my initial DAX to one using groupby instead.
    Thank you

    Saturday, September 21, 2019 3:18 PM
  • I’m interested to know why you want to do that. Regardless, group by takes a table as an input, just like summarize. Summarize returns a table, so just use your DAX above as the first parameter to group by

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Saturday, September 21, 2019 9:12 PM
    Answerer
  • I’m interested to know why you want to do that. Regardless, group by takes a table as an input, just like summarize. Summarize returns a table, so just use your DAX above as the first parameter to group by

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Apologies...
    I believe I see what summarize does now.
    Since summarize does grouping then what is the point of using group by (I am trying to see the point of learning group by in DAX)

    Thanks

    Sunday, September 22, 2019 6:38 AM
  • Hi

    Please find the differences between Summarize and Group by listed below.

    Besides performance, a computational limit of SUMMARIZE is that it cannot aggregate values calculated dynamically within the query itself. 

    The new DAX introduces the GROUPBY function, which has a syntax similar to SUMMARIZE, even if its semantic is a different one. In fact, it solves the issues we had in SUMMARIZE when grouping values, so you can avoid the pattern ADDCOLUMNS/SUMMARIZE.

    For detailed explanation please go through below blog which helps to differentiate both in a unique way.

    https://www.sqlbi.com/articles/nested-grouping-using-groupby-vs-summarize/

    Hope this is helpful !!
    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Sunday, September 22, 2019 6:47 AM
  • Hi

    Please find the differences between Summarize and Group by listed below.

    Besides performance, a computational limit of SUMMARIZE is that it cannot aggregate values calculated dynamically within the query itself. 

    The new DAX introduces the GROUPBY function, which has a syntax similar to SUMMARIZE, even if its semantic is a different one. In fact, it solves the issues we had in SUMMARIZE when grouping values, so you can avoid the pattern ADDCOLUMNS/SUMMARIZE.

    For detailed explanation please go through below blog which helps to differentiate both in a unique way.

    https://www.sqlbi.com/articles/nested-grouping-using-groupby-vs-summarize/

    Hope this is helpful !!
    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Hi, thanks for the details...
    Does this mean that instead of my original summarize DAX mentioned in my post, I have to use group by always and avoid summarize?
    Thank you
    Sunday, September 22, 2019 7:04 AM
  • That's true - its preferred to be.

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Sunday, September 22, 2019 8:44 AM
  • That's true - its preferred to be.

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    In that case, my original question still remain which is:
    How can my original summarize DAX be written using groupby DAX ?

    Thank you

    Sunday, September 22, 2019 8:55 AM
  • Try with the below and let me know how it goes

     GROUPBY (
            'TablePH',
            'TableP'[Field1],
            'TableP'[Field3],
    	"Hours", [Time]
            "CountOfCountries", DISTINCTCOUNT (CURRENTGROUP(), 'Table6'[Country] )
        )

    You can change the column list if required, go through the below documentation for more details.

    https://docs.microsoft.com/en-us/dax/groupby-function-dax

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Sunday, September 22, 2019 9:36 AM
  • Does not seem to be correct.

    single value for column 'Table6'[Country] can not be determined

    Monday, September 23, 2019 7:48 AM
  • Hi

    Could you try once with the below.

    CALCULATE(DISTINCTCOUNT('Table6'[Country]), GROUPBY('TablePH','TableP'[Field1],'TableP'[Field3],"Hours", [Time]))

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    • Marked as answer by arkiboys Wednesday, September 25, 2019 3:57 PM
    Wednesday, September 25, 2019 2:48 PM
  • thank you
    Wednesday, September 25, 2019 3:57 PM