Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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
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
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
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |