cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Solution Sage

## How can i get the correct values?

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

Quality over Quantity

1 ACCEPTED SOLUTION
Super User

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

2 REPLIES 2
Super User

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

Solution Sage

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.

Quality over Quantity

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors