cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper II

## FILTER IN GROUP BY

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.

1 ACCEPTED SOLUTION
Solution Sage

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.

Solution Sage

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors