SQL to DAX group by

[Anonymous]

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!

[v-jayw-msft · Accepted solution]

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]

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.

[Anonymous]

@v-jayw-msftUnfortuntelly, it's not working, I need to have 9 in each raw.

a	9
b	9
c	9

[v-jayw-msft]

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]

@v-jayw-msftno:( no it's just showing 1 in all columns...

a	1
b	1
c	1

[v-jayw-msft]

Hi @Anonymous ,

Ok...how about this.

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

 

If it still wrong, better to share some sample data to us.

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.

[mwegener]

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]

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

@mwegener  I'm trying to achive this

	filter	column 1	column 2	%
	filter 1	1	15	=column1/column2'
	filter 2	2	15	=column1/column2'
	filter 3	3	15	=column1/column2'
	filter 4	4	15	=column1/column2'
	filter 5	5	15	=column1/column2'
total		15

Thanks a lot in advance!

[Anonymous]

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().