Exclude lines based on multiple criteria

[gracechong]

Hi,

With below as sample data, my goal is to exlude the first line in sample data shown; whereby the multiple criteria for excluding is:

Line Type = Actual

Project Type = Funded

Exception = Yes

Cost A + Cost B = 0

I did try to modify based on what I can understand from other question threads but I don't think I'm doing it right.

Can someone help to point out what is the mistake in the below measure?

Country	Project	Manager	Cost A	Cost B	Line Type	Project Type	Exception
Japan	123456	21235	0	0	Actual	Funded	Yes
Japan	123456	21235	50000	0	Projection	Funded	Yes
Vietnam	445566	66776	300000	60000	Projection	Not Funded	Yes
South Korea	777333	77889	0	0	Actual	Funded	No
Bhutan	335566	23467	70000	0	Actual	Funded	Yes

Measure =
VAR Summ =
    SUMMARIZE (
        data,
        data[Country],
        data[Project],
        data[Manager],
        data[Line Type],
        data[Project Type],
 data[Exception],
        "A", [Cost A],
        "B", [Cost B]
    )
RETURN
 
    COUNTROWS (
        FILTER (
            Summ,
            AND( data[Line Type] = "Actual", data[Project Type] = "Funded", data[Exception] = "Yes")
                && OR ( [A] <> 0, [B] <> 0 )
        )
    )

[Fowmy · Accepted solution]

@gracechong

You can create this measure and assign it to the table visual in the filter pane and set it to 1:

New Measure = 
 IF(
     MAX(data[Line Type]) = "Actual" && 
     MAX(data[Project Type]) = "Funded" && 
     MAX(data[Exception]) = "Yes" && 
     ( MAX(data[Cost A]) <> 0, MAX(data[Cost B] <> 0 )),
     1,
     0
)

________________________

If my answer was helpful, consider Accepting it as the solution to help other members find it

Click the Thumbs-Up icon if you like this answer 🙂

Youtube Linkedin