Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous_226
Frequent Visitor

Getting differences between 2 outputs in Dax

Some context:
I am connected Direct Query to a dataset so i cannot use calculated columns or change the dataset in any way. I need to accomplish this in dax.

 

I have a measure that shows me all the tickets touched by "Team1"

      Note: The "MyTeam" table contains many different ticket numbers but the same ticket number can also repeat in the table for each team that touches it is a new row in the table.

 

TouchedByTeam1 = CALCULATETABLE(VALUES(MyTable[Ticket]), FILTER(MyTable, MyTable[Definition] = "Team Group"), FILTER(MyTable, MyTable[Team] = "Team 1"))


I have another measure that gets me al the tickets Team 1 touches but doesnt not move to another team.

 

FirstTeam1Touch =
VAR LastTouchTeam =
CALCULATE(
MAXX(MyTable, MyTable[Team]),
FILTER(
MyTable,
MyTable[Start] = CALCULATE(MAX(MyTable[Start]), ALLEXCEPT(MyTable, MyTable[Number]))
)
)
VAR FirstTeam1Touch =
CALCULATE(
MIN(MyTable[Start]),
MyTable[Team] = "Team 1",
ALLEXCEPT(MyTable, MyTable[Ticket])
)
RETURN
IF(
LastTouchTeam = "Team1",
FirstTeam1Touch,
BLANK()
)

I have been at this for the last couple weeks and havent figured it out. I put both of these measure in the same Measure as variables and have been trying to use "Except" to get the difference but nothing seems to work. This was my last attempt with everything in variables. Obvisously the Except doesnt work becuase the FirstTeam1Touch does not output a table being it is a calculate statement.

 

VAR LastTouchTeam =
    CALCULATE(
        MAXX(MyTable, MyTable[Team]),
        FILTER(
            MyTable,
            MyTable[Start] = CALCULATE(MAX(MyTable[Start]), ALLEXCEPT(MyTable, MyTable[Ticket]))
        )
    )
VAR FirstTeam1Touch =
    CALCULATE(
        MIN(MyTable[Start]),
        MyTable[Team] = "Team 1",
        ALLEXCEPT(MyTable, MyTable[Ticket])
    )

 

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))

 

var total = CALCULATETABLE(EXCEPT(TouchedByTeam1, FirstTeam1Touch))

Any help is greatly appreciated.

1 REPLY 1
technolog
Super User
Super User

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.

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors