Forum Discussion
Getting differences between 2 outputs in Dax
I understand your problem. You're trying to get the difference between two sets of tickets: those touched by Team1 and those that Team1 touched but didn't move to another team. The main issue you're facing is that the EXCEPT function expects two tables, but FirstTeam1Touch is returning a scalar value, not a table.
Let's break this down a bit.
First, we need to ensure that both measures return tables so that we can use the EXCEPT function.
For the FirstTeam1Touch, instead of returning the MIN(MyTable[Start]), let's return the tickets where Team1 was the last to touch. This will give us a table of tickets.
Here's how you can modify the FirstTeam1Touch measure:
FirstTeam1TouchTable =
FILTER(
ALL(MyTable[Ticket]),
VAR CurrentTicket = MyTable[Ticket]
VAR LastTouchTeam =
CALCULATE(
MAXX(MyTable, MyTable[Team]),
FILTER(
MyTable,
MyTable[Start] = CALCULATE(MAX(MyTable[Start]), ALLEXCEPT(MyTable, MyTable[Ticket]))
)
)
RETURN
LastTouchTeam = "Team1" && CALCULATE(COUNTROWS(MyTable), MyTable[Team] = "Team 1", MyTable[Ticket] = CurrentTicket) > 0
)
Now, FirstTeam1TouchTable will return a table of tickets where Team1 was the last to touch.
With this, you can now use the EXCEPT function to get the difference:
Difference =
VAR TouchedByTeam1 = CALCULATETABLE(
VALUES(MyTable[Ticket]),
FILTER(MyTable, MyTable[Definition] = "Team Group"),
FILTER(MyTable, MyTable[Team] = "Team 1"),
FILTER(MyTable, MyTable[Created] >= EOMONTH(TODAY(),-2)+1 && MyTable[Created] < EOMONTH(TODAY(),-1)+1)
)
RETURN
CALCULATETABLE(EXCEPT(TouchedByTeam1, FirstTeam1TouchTable))
This Difference measure should now give you the tickets touched by Team1 but not those where Team1 was the last to touch.