cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EricHulshof
Solution Sage
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 1021-5-200725-01-2008
Project 1025-1-200812-09-2009
Project 1112-9-200931-12-2100
Project 216-10-201931-12-2100
Project 3015-4-201017-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 3015-4-201017-08-2010

 

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


Quality over Quantity


Did I answer your question? Mark my post as a solution!


1 ACCEPTED SOLUTION
AlB
Super User
Super User

Hi @EricHulshof 

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  Datanaut

  

View solution in original post

2 REPLIES 2
AlB
Super User
Super User

Hi @EricHulshof 

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  Datanaut

  

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


Did I answer your question? Mark my post as a solution!


Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors