How can i get the correct values?

[Anonymous]

Hi, Sorry for the vague title, but im not sure how to rewrite it.

The sample dataset to explain the problem looks like this:

Project    Active   Active from   Active to

Project 1	0	21-5-2007	25-01-2008
Project 1	0	25-1-2008	12-09-2009
Project 1	1	12-9-2009	31-12-2100
Project 2	1	6-10-2019	31-12-2100
Project 3	0	15-4-2010	17-08-2010

The idea is that i want to see all project have a 0 but no 1. in this example i only want to show project 3. As project 1 got a 1 in 2009 and project 2 also has a 1. 

So the expected result should be:

Project 3

0

15-4-2010

17-08-2010

Please note that projects can have multiple 1's and 0's 

[AlB · Accepted solution]

Hi @Anonymous 

You could create a new calculated table with only the rows of interest or do it with a visual  and a measure. Let's look at the latter:

1. Place all columns of your table in a table visual (make sure they all are set to "Don't summarize"

2. Create this measure:

ShowMeasure =
VAR Count0_ =
    CALCULATE (
        COUNT ( Table1[Project] ),
        Table1[Active] = 0,
        ALLEXCEPT ( Table1, Table1[Project] )
    )
VAR Count1_ =
    CALCULATE (
        COUNT ( Table1[Project] ),
        Table1[Active] = 1,
        ALLEXCEPT ( Table1, Table1[Project] )
    )
RETURN
    IF ( Count0_ > 0 && Count1_ = 0, 1, 0 )

3. Include  the measure in the visual filter and choose to show when it is 1 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  

[Anonymous]

Thankyou, i used your code to create a calculated column instead of a measure. this worked better on the whole dataset with relationships e.d.