FILTER IN GROUP BY

[georgec96]

Hi everyone,

I just started working with DAX and I came across something and I'm a bit stuck.

Is there any way to use a filter function when using group by? I've been searching for it for days but couldn't find anything.

I would like to group my data by date, usr_id, shift, rate_type and

do a sum of the total seconds column where column rate type starts with "replens" and column actcod="GENMOV" and "CASRPL"

do a count of rows where column actcod = "CASRPL" and column Time over/under = "Time Under 5".

What i am struggling to understand is how to apply a different fitler for those 2 aggregations 
I want a filter on sum and a different filter on count.

Thanks.

[Whitewater100 · Accepted solution]

Hello:

Here is one way to do this. 

TWO CALC COLUMNS

A = IF(
   AND( LEFT( Table[rate-type], 7) = “replens”,
       Table[actcod] = “GENMOV”),
   SUM(Table[Total Seconds], BLANK()
)

B =IF(

   AND(Table[actcod] = “CASRPL”,

       Table[Time over/under] = “Time Under 5”),

   1), BLANK()

)

TWO Measures

Sum A = SUM(Table[A])

SumB =SUM(Table[B])

You could potentially just have two measures where you can have variables for the conditions

But here you can see what is occurring a bit easier.