Forum Discussion
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
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
2 Replies
- AlB
Community Champion
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