Forum Discussion
Count Wows with Matching Values
I need a bit of dax-fu. I have GANTT chart with some overlapping milestones and I'd love to be able to create (my guess is) a measure that will exclude the row if a different milestone has the same date. I have a hunch on how to do it, but I'm not sure how I can pass the current rows date to the CountRows(Filter()) formula.
Example, due to some rushed projects if Epic Troika starts the same day as Development (I know this is unreasonable, but we work in the world we're given), I'd just like the Development milestone to show. The order would be Development overwrites Epic Troika which overwrites Discovery.
'Milestones' table
| ProjectID | ProjectName | Milestone | StartDate |
| 1 | Project1 | 1/1/2023 | |
| 1 | Project1 | Discovery | 2/1/2024 |
| 1 | Project1 | Epic Troika | 3/1/2024 |
| 1 | Project1 | Development | 3/1/2024 |
I solved my own problem! I forgot about the existence of CALCULATE(SELECTEDVALUE()). For anyone else reading this in the future, I actually wanted a Column rather than a Measure (since I wanted every row to be tested). Here's the pseudocode:
VAR _CurrentDate = CALCULATE(SELECTEDVALUE(Milestones[StartDate]))
VAR _CurrentProject = CALCULATE(SELECTEDVALUE(Milestones[ProjectID]))
VAR _CurrentMilestone = CALCULATE(SELECTEDVALUE(Milestones[Milestone]))VAR _MatchingRows =
SWITCH(
_CurrentMilestone,"Epic Troika",
COUNTROWS(
FILTER(
Milestones,Milestones[ProjectID] = _CurrentProject
&& Milestones[StartDate] = _CurrentDate
&& Milestones[Milestone] = "Development"
)
),...
)RETURN IF(_MatchingRows > 0, "Exclude", BLANK())
This is obv not the cleanest code in the world, but we only have a few hundred rows, and they'll likely change their mind on what we're reporting in the next few weeks anyway. 😄
3 Replies
ronnie_roberts How are you determining precedence? Is it arbitrary? Seems like you would need a separate table to store that.
Hi Greg, (sorry, I posted that before leaving for the day)
Managment only cares about those 3 milestones, so I kinda figured I was just going to do either a switch or an embedded if-statement like (pseudocode):
VAR _NumberOfTimes=
Switch(
Milestones[Milestone],"Epic Troika", Countrows(Filter(Milestones, [ProjectID] = [[[Current ProjectID]]], [Milestone]="Development", [StartDate] = [[[Current StartDate]]])),
...
)RETURN If(_NumberOfTimes > 1, "Exclude", BLANK())
I just can't figure out how to pass those [[[ ]]] values. Hopefully this makes some sense!
I solved my own problem! I forgot about the existence of CALCULATE(SELECTEDVALUE()). For anyone else reading this in the future, I actually wanted a Column rather than a Measure (since I wanted every row to be tested). Here's the pseudocode:
VAR _CurrentDate = CALCULATE(SELECTEDVALUE(Milestones[StartDate]))
VAR _CurrentProject = CALCULATE(SELECTEDVALUE(Milestones[ProjectID]))
VAR _CurrentMilestone = CALCULATE(SELECTEDVALUE(Milestones[Milestone]))VAR _MatchingRows =
SWITCH(
_CurrentMilestone,"Epic Troika",
COUNTROWS(
FILTER(
Milestones,Milestones[ProjectID] = _CurrentProject
&& Milestones[StartDate] = _CurrentDate
&& Milestones[Milestone] = "Development"
)
),...
)RETURN IF(_MatchingRows > 0, "Exclude", BLANK())
This is obv not the cleanest code in the world, but we only have a few hundred rows, and they'll likely change their mind on what we're reporting in the next few weeks anyway. 😄
