Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Alex_0201
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
harshnathani
Community Champion
Community Champion

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
harshnathani
Community Champion
Community Champion

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

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
)

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!

 

 

 

Thank you very much!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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