Forum Discussion
Exclude lines based on multiple criteria
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 )
)
)
@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 🙂
1 Reply
@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 🙂