cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Partisan
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
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

View solution in original post

4 REPLIES 4
Highlighted
Super User V
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

View solution in original post

Highlighted
Post Partisan
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
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)

Did I answer your question? Mark my post as a solution!

 

 

 

Highlighted
Post Partisan
Post Partisan

Re: Measure with filters

Thank you very much!

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

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?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

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

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