cancel
Showing results for
Did you mean:
Highlighted Post Partisan

## Measure with filters

Hi! I have a table called DM_ACTIVITY with several columns two of which are activity_duration and activity_group.

I would like to create a measure in the report mode that sums up all the activity_duration hrs filtered by three values in the activity_group column which are RIG, D1C and D2C.

activity_group column has text data type and doesn't summarize values.

activity_duration has decimal number data type and doesn't summarize values (tried Sum as well).

Why doesn't this measure work?

Total  =
CALCULATE (
SUM ( 'DM_ACTIVITY'[activity_duration]),
FILTER (
'DM_ACTIVITY',
'DM_ACTIVITY'[activity_group] = "RIG"
&& 'DM_ACTIVITY'[activity_group] = "D1C"
&& 'DM_ACTIVITY'[activity_group] = "D2C"
)
) / 24
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Super User V

## Re: Measure with filters

Hi @Alex_0201 ,

Try this measure

``````Total =
VAR a =
CALCULATE (
SUM ( 'DM_ACTIVITY'[activity_duration] ),
'DM_ACTIVITY'[activity_group]
IN {
"RIG",
"D1C",
"D2C"
}
)
RETURN
DIVIDE (
a,
24
)``````

or

``````Total =
VAR a =
SUMX (
FILTER (
'DM_ACTIVITY',
'DM_ACTIVITY'[activity_group]
IN {
"RIG",
"D1C",
"D2C"
}
),
'DM_ACTIVITY'[activity_duration]
)
RETURN
DIVIDE (
a,
24
)``````

Regards,

Harsh Nathani

4 REPLIES 4
Highlighted Super User V

## Re: Measure with filters

Hi @Alex_0201 ,

Try this measure

``````Total =
VAR a =
CALCULATE (
SUM ( 'DM_ACTIVITY'[activity_duration] ),
'DM_ACTIVITY'[activity_group]
IN {
"RIG",
"D1C",
"D2C"
}
)
RETURN
DIVIDE (
a,
24
)``````

or

``````Total =
VAR a =
SUMX (
FILTER (
'DM_ACTIVITY',
'DM_ACTIVITY'[activity_group]
IN {
"RIG",
"D1C",
"D2C"
}
),
'DM_ACTIVITY'[activity_duration]
)
RETURN
DIVIDE (
a,
24
)``````

Regards,

Harsh Nathani

Highlighted Post Partisan

## Re: Measure with filters

Hi @harshnathani Thanks! I used the first one.

What if I need add another filter (value "P") from another column (activity_class) in the same table?

what's wrong with this formula?

Prod time =
VAR a =
CALCULATE (
SUM ( 'DM_ACTIVITY'[activity_duration] ),
'DM_ACTIVITY'[activity_group]
IN {
"RIG",
"D1C",
"D2C"
}
&& 'DM_ACTIVITY'[activity_class]
IN {
"P"
}
)
RETURN
DIVIDE (
a,
24
)
Highlighted Super User V

## Re: Measure with filters

HI @Alex_0201 ,

Just a slight modification then.

``````Prod time =
VAR a =
CALCULATE (
SUM ( 'DM_ACTIVITY'[activity_duration] ),
FILTER (
'DM_ACTIVITY',
'DM_ACTIVITY'[activity_group]
IN {
"RIG",
"D1C",
"D2C"
}
&& 'DM_ACTIVITY'[activity_class]
IN {
"P"
}
)
)
RETURN
DIVIDE (
a,
24
)``````

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Highlighted Post Partisan

## Re: Measure with filters

Thank you very much!

Announcements #### Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users. #### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge! #### Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month. #### Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5. Top Solution Authors
Top Kudoed Authors
Users online (919)