Filter by label to calculate the progress %

Questions & Answers
, ,
1

Hello,

I need help for my defined measure “sum of progress %” it’s a measure to calculate progress by using issue due and a free_field_choice_01 by filtering by component, département and Label.
The problem is when I filter on a specific Labels, it’s doesn’t work the measure calculate all the issues.

How can I filter on issues with a specific Label ? (I have 2 labels : 2024 and 2023)

Thank you for your help :

CoalesceEmpty(
Cache(
Sum(
Filter(
Descendants([Issue].Currentmember, [Issue].[Issue]),
NOT IsEmpty([Measures].[Issue due date])
AND
(
IsEmpty([Measures].[Issue closed date])
OR
DateBeforePeriodEnd([Measures].[Issue closed date],[Time].CurrentHierarchyMember)
)
AND
(
IIf([Component].CurrentHierarchyMember.Level.Name=‘Component’,
[Measures].[Issue components]=[Component].CurrentHierarchyMember.Name,
CBool(1))
)
AND
(
IIf([Département].CurrentHierarchyMember.Level.Name=‘Département’,
[Measures].[Issue Département IRS]=[Département IRS].CurrentHierarchyMember.Name,
CBool(1))
)
),
IIf(NOT IsEmpty([Measures].[Issue closed date]),
1,
CASE
WHEN ([Measures].[Issues history],[free_field_choice_01].[100]) > 0 THEN 1
WHEN ([Measures].[Issues history],[free_field_choice_01].[80]) > 0 THEN 0.8
WHEN ([Measures].[Issues history],[free_field_choice_01].[60]) > 0 THEN 0.6
WHEN ([Measures].[Issues history],[free_field_choice_01].[40]) > 0 THEN 0.4
WHEN ([Measures].[Issues history],[free_field_choice_01].[20]) > 0 THEN 0.2
WHEN ([Measures].[Issues history],[free_field_choice_01].[0]) > 0 THEN 0.0000001
WHEN ([Measures].[Issues history],[free_field_choice_01].[(none)]) > 0 THEN 0.0000001
END
)
))
,0)

2

Hi @Megacreation

Please try this formula with the added check for the label:

CoalesceEmpty(
    Cache(
        Sum(
            Filter(
                Descendants([Issue].Currentmember, [Issue].[Issue]),
                NOT IsEmpty([Measures].[Issue due date])
                AND
                    (
                        IsEmpty([Measures].[Issue closed date])
                        OR
                        DateBeforePeriodEnd([Measures].[Issue closed date],[Time].CurrentHierarchyMember)
                    )
                AND
                    (
                    IIf([Component].CurrentHierarchyMember.Level.Name='Component',
                    [Measures].[Issue components]=[Component].CurrentHierarchyMember.Name,
                    CBool(1))
                    )
                AND
                    (
                    IIf([Département].CurrentHierarchyMember.Level.Name='Département',
                    [Measures].[Issue Département IRS]=[Département IRS].CurrentHierarchyMember.Name,
                    CBool(1))
                    )
                AND
                    CoalesceEmpty(
                        [Issue].CurrentHierarchyMember.Get('Labels'),""
                    ) MATCHES ".*"||[Label].CurrentMember.Name||".*"
            ),
            IIf(
                NOT IsEmpty([Measures].[Issue closed date]),
                1,
                CASE
                WHEN ([Measures].[Issues history],[free_field_choice_01].[100]) > 0 THEN 1
                WHEN ([Measures].[Issues history],[free_field_choice_01].[80]) > 0 THEN 0.8
                WHEN ([Measures].[Issues history],[free_field_choice_01].[60]) > 0 THEN 0.6
                WHEN ([Measures].[Issues history],[free_field_choice_01].[40]) > 0 THEN 0.4
                WHEN ([Measures].[Issues history],[free_field_choice_01].[20]) > 0 THEN 0.2
                WHEN ([Measures].[Issues history],[free_field_choice_01].[0]) > 0 THEN 0.0000001
                WHEN ([Measures].[Issues history],[free_field_choice_01].[(none)]) > 0 THEN 0.0000001
                END
            )
        )
    )
    ,0
)

​Best regards,
​Nauris

3

Thanks you but it’s not working it give me the value.
for the part AND
CoalesceEmpty(
[Issue].CurrentHierarchyMember.Get(‘Labels’),“”
) MATCHES “.“||[Label].CurrentMember.Name||”.”
I did put my labels (2024 and 2023) but still not working

4

Hi @Megacreation

The previous formula was intended for one selected label in the Page filters.

For two selected labels in the Page filter, please try this formula:

CoalesceEmpty(
    Cache(
        Sum(
            Filter(
                Descendants([Issue].Currentmember, [Issue].[Issue]),
                NOT IsEmpty([Measures].[Issue due date])
                AND
                    (
                        IsEmpty([Measures].[Issue closed date])
                        OR
                        DateBeforePeriodEnd([Measures].[Issue closed date],[Time].CurrentHierarchyMember)
                    )
                AND
                    (
                    IIf([Component].CurrentHierarchyMember.Level.Name='Component',
                    [Measures].[Issue components]=[Component].CurrentHierarchyMember.Name,
                    CBool(1))
                    )
                AND
                    (
                    IIf([Département].CurrentHierarchyMember.Level.Name='Département',
                    [Measures].[Issue Département IRS]=[Département IRS].CurrentHierarchyMember.Name,
                    CBool(1))
                    )
                AND
                IIf(Count(ChildrenSet([Label].CurrentMember))=2,
                (
                    CoalesceEmpty(
                        [Issue].CurrentHierarchyMember.Get('Labels'),""
                    ) MATCHES ".*"|| ChildrenSet([Label].CurrentMember).Item(0).Name ||".*"                  
                  OR
                    CoalesceEmpty(
                        [Issue].CurrentHierarchyMember.Get('Labels'),""
                    ) MATCHES ".*"|| ChildrenSet([Label].CurrentMember).Item(1).Name ||".*"
                ),
                0)
            ),
            IIf(
                NOT IsEmpty([Measures].[Issue closed date]),
                1,
                CASE
                WHEN ([Measures].[Issues history],[free_field_choice_01].[100]) > 0 THEN 1
                WHEN ([Measures].[Issues history],[free_field_choice_01].[80]) > 0 THEN 0.8
                WHEN ([Measures].[Issues history],[free_field_choice_01].[60]) > 0 THEN 0.6
                WHEN ([Measures].[Issues history],[free_field_choice_01].[40]) > 0 THEN 0.4
                WHEN ([Measures].[Issues history],[free_field_choice_01].[20]) > 0 THEN 0.2
                WHEN ([Measures].[Issues history],[free_field_choice_01].[0]) > 0 THEN 0.0000001
                WHEN ([Measures].[Issues history],[free_field_choice_01].[(none)]) > 0 THEN 0.0000001
                END
            )
        )
    )
    ,0
)