how to achieve group by and 'having' in dax?

[Anonymous]

I have the below table:

I need to:

• Sum the hours per type and week.
• Then, I need to filter the users that have more than x hours in certain types.

I should check for both: either next week or next next week.

I can do it easily in sql *

SELECT Sum(hours), Key_user, Type
FROM Fact f JOIN DimDate d ON f.Key_date = d.Key_date
WHERE  d.week = (SELECT week + 1 FROM DimDate WHERE  date = CONVERT(DATE, Getdate()))
GROUP  BY Key_user, Type
HAVING(Sum(hours) >10 and Type = 'AA') or (Sum(hours) >15 and Type = 'AAA')

Can this be done as a measure in DAX?

*(I shall add a union all and do it for Week +2 too)

[Anonymous · Accepted solution]

Hi  @Anonymous 

I created some data:

Custom rules:

Filter users in the "AAA" type over 150 hours

Here are the steps you can follow：

1. Create calculated column.

Week = WEEKNUM('Table'[Date])

2. Create measure.

Sum_Week_Type =
CALCULATE(SUM('Table'[Houres]),FILTER(ALL('Table'),'Table'[Week]=MAX('Table'[Week])&&'Table'[Type]=MAX('Table'[Type])))

If =
IF(
    MAX('Table'[Type])="AAA"&&[Sum_Week_Type]<=150 ,1,0)

3. Place Measure[IF] in Filter, set is =1, apply filter.

4. Result:

Best Regards,

Liu Yang

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

[amitchandak]

@Anonymous , Try a measure like

sumx(filter(summarize(Fact , Fact[keyuser], fact[type],"_1", sum(fact[hours])),([_1] >10 && [Type] = "AA") || [_1] >15 && [Type] = "AAA"),[_1])

Correct _1 measure for week data.

also join fact and date, if required

[Yanskie]

Thanks @amitchandak, it works like a charm!
just to rewrite your measure in a easier-for-reading way (90% the same as your example measure):

sample table is like this:

type      hours

a             1

a             2

b             1

b             2

b             3

c              2

c              1

c              2

measure like this    

test_1 =

SUMX(

filter(

SUMMARIZECOLUMNS(

Table_test[type],

"total_hours", SUM(Table_test[hours])

),

[total_hours] > 3),

[total_hours])

(it returns 11 in my PBI desktop card visual:  B total is 6 + C total is 5 = 11.  A's total is 3, which is not more than 3 so A's total is excluded from result)