Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

SQL to DAX group by

Hi, could you please help me to rewrite this code in dax?

 

select
sum(dates) from
(select
filter,
count(date) as dates
from db
group by filter) tmp

 

Please!!!! Thanks a lot in advance!

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

     

    Please Try

    count = CALCULATE(COUNT('Table'[date]),ALLEXCEPT('Table','Table'[filter]))

     

     

    Best Regards,

    Jay

    Community Support Team _ Jay Wang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    Please Try

    count = CALCULATE(COUNT('Table'[date]),ALLEXCEPT('Table','Table'[filter]))

     

     

    Best Regards,

    Jay

    Community Support Team _ Jay Wang

    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
      6 years ago

      AnonymousUnfortuntelly, it's not working, I need to have 9 in each raw.

      a9
      b9
      c9
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Anonymous ,

         

        Please modify the formula as below.

        count = CALCULATE(COUNT('Table'[date]),ALLSELECTED('Table'[filter]))

         

        Best Regards,

        Jay

        Community Support Team _ Jay Wang

        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
      6 years ago

      Thank you! I made this work...I think so...But it should be measure, not column.

      Thanks a lot!!!I'm still testing, but I hope, that it is working.

  • mwegener's avatar
    mwegener
    Most Valuable Professional
    6 years ago

    Hi Anonymous ,

     

    it should look something like this.

    Measure =
SUMX ( 
		GROUPBY ( db, db[filter], "dates", COUNT ( db[date] ) )
	, [dates] )

    Regards,

    Marcus

    Dortmund - Germany
    If I answered your question, please mark my post as solution, this will also help others.
    Please give Kudos for support. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      I'm getting this error...

      Function 'GROUPBY' scalar expressions have to be Aggregation functions over CurrentGroup(). The expression of each Aggregation has to be either a constant or directly reference the columns in CurrentGroup().

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      mwegener  Unfortunatelly, I'm getting this error 

       

      Function 'GROUPBY' scalar expressions have to be Aggregation functions over CurrentGroup(). The expression of each Aggregation has to be either a constant or directly reference the columns in CurrentGroup().

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        mwegener  I'm trying to achive this

         filtercolumn 1column 2%
         filter 1115=column1/column2'
         filter 2215=column1/column2'
         filter 3315=column1/column2'
         filter 4415=column1/column2'
         filter 5515=column1/column2'
        total 15  

        Thanks a lot in advance!