
1. Re: Filter 1 Column based on data from different column
Michael Hesser Jun 23, 2016 9:38 AM (in response to Michael Lahey)3 of 3 people found this helpfulHi Michael;
I was able to solve it this way:
I created a small LOD calculation:
Then I dropped this onto the Filters column and chose "False"
This prevented anything with a Cancel in Task from displaying.
Hope this works!

2. Re: Filter 1 Column based on data from different column
Simon Runc Jun 23, 2016 9:42 AM (in response to Michael Lahey)2 of 2 people found this helpfulhi Michael,
Unfortunately I only have 9.0 and 9.3 still installed, so the attachment is in 9.3.
So yes (although there are a few ways we could do this) sets is my preferred method. For a conditional set we need it to be a Boolean test, and the test has to be an aggregate. This way we can create a test on and aggregate, but the set in/out will be returned at the id level...
So I created a set on ID
I then used the following formula as the condition of the set
So the inner part is putting 1 or 0 next to each row, depending of whether the row = 'Cancel'...it then takes the MAX of this by id...and if it =0 (i.e. the id has no task cancel) they get put in the set. As this is returned at id level, the entire id (not just the task = cancel row) is excluded. This is a very useful technique...so much so there is an entire video (with loads of great usecases, and explaining this much better than I can!)
Think Data Thursday  November 20  Let's talk about Sets Baby!
Hope this does the trick and makes sense. If not (on either front) please post back

3. Re: Filter 1 Column based on data from different column
Simon Runc Jun 23, 2016 9:44 AM (in response to Michael Lahey)...Just to let you know, mine and other Michael's solutions are actually the same....sets were the FIXED LoDs, before we had FIXED LoDs!! (I'm just a little old fashioned!!)

4. Re: Filter 1 Column based on data from different column
Michael Lahey Jun 23, 2016 9:52 AM (in response to Michael Lahey)Thank you both!

5. Re: Filter 1 Column based on data from different column
Michael Hesser Jun 23, 2016 12:42 PM (in response to Simon Runc)This is great, Simon Runc. I want to learn a little more about sets ("Let's talk about Sets, Baby") so I can use them more easily in my daytoday operations.

6. Re: Filter 1 Column based on data from different column
Michael Lahey Jun 23, 2016 3:09 PM (in response to Michael Lahey)My next step (that i thought I could handle myself ) is to get the max duration task per ID.
I tried doing an LOD calc, but can't seem to figure out what to use. {INCLUDE [Task]:ATTR([Duration])=MAX([Duration])} > can't use because ATTR is invalid in LOD
{INCLUDE [Task]: MAX([Duration])} > just gives the max duration if there is more than 1 per task
Can't seem to figure out what calc to use for a set.
Any more insight on this?

Task Exclude.twbx 15.9 KB


7. Re: Filter 1 Column based on data from different column
Simon Runc Jun 24, 2016 1:36 AM (in response to Michael Lahey)1 of 1 people found this helpfulhi Michael,
So we can do this, as you've nearly done, with LoDs. In the below I've done this over a couple of formulas to help the understanding, but in your final version you can nest into one (if you like!)
So first we need to get the MAX Duration, by ID...so that we can later match this against the MAX task
[Max Duration  SR]
{FIXED [ID]: MAX(IIF([Exlude if Cancel],[Duration],NULL))}
btw I've added the check within the formula for the ID not having Cancel as one of their tasks. If you are using that set as a filter, you can ignore this bit and just becomes
{FIXED [ID]: MAX([Duration])}
So you can now see this puts the MAX duration task duration against every ID.
So we can then do a test on this
[Max Duration Task]
IF [Duration]=[Max Duration  SR] THEN [Task] ELSE NULL END
so this field only returns the Max duration Task...btw I've assumed no aggregation is required for the Duration (i.e. that there is only one row per ID/Task). This keeps all the calculations at row level, so work regardless of the vizLoD. If we did want to aggregate this, but keep the calculations at row level, we could create another LoD
I've shown what this looks like (filtering out nonmax tasks) in the 'final' tab. You'll need to think how you want to handle where there are 2 max tasks for an ID

8. Re: Filter 1 Column based on data from different column
Michael Lahey Jun 24, 2016 6:56 AM (in response to Simon Runc)Ah, should have stuck with the logic and thought about the max per ID instead of task level.
Thanks again for the insight!