FILTER IN GROUP BY

georgec96 — Sun, 13 Mar 2022 19:21:01 GMT

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.

Re: FILTER IN GROUP BY

Whitewater100 — Sun, 13 Mar 2022 20:39:27 GMT

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.

topic FILTER IN GROUP BY in DAX Commands and Tips

FILTER IN GROUP BY

Re: FILTER IN GROUP BY