DAX Measure Exception Report


Seeking some assistance or direction to any tutorials with similar concepts.

I am trying to create an exception report. I have various tasks with a Created Date, Due Date & Complete Date.

Currently my dates filter is actively connected to the complete date and inactively connected to the Created date.

I am trying to find out which incomplete tasks have remained outstanding for 3, 5 or longer than 10 working days.

Looking through the forum these are the measures i have so far:

Total Incomplete Tasks =
COUNT ( AcquistionsThreads[TaskID] ),
    AcquistionsThreads[TaskStatus] <> "Complete"
        && AcquistionsThreads[TaskStatus] <> "Aborted"
Task Days Lapsed = 
VAR StartDate =
    MIN ( AcquistionsThreads[TaskCreatedDate] )
VAR DueDate =
    TODAY ()
        COUNTROWS ( Dates ),
        FILTER ( ALL ( Dates ), Dates[Date] >= StartDate && Dates[Date] < DueDate ),
        FILTER ( ALL ( Dates ), Dates[IsworkingDay] = 1 )
Days Lapsed Incomplete Tasks =
VAR taskstatus =
    SELECTEDVALUE ( AcquistionsThreads[TaskStatus] )
    SWITCH (
        TRUE (),
        taskstatus = "Complete", BLANK (),
        taskstatus = "Aborted", BLANK (),
        [Task Days Lapsed]

I’m not sure how i can connect the measures to get it into a format :



Can you upload some sample data? At first glance I think using EXCEPT in a calculated table would be the way to go, but that’s just guess w/o seeing any data.



Thanks for your quick response.

Attached as requested.Data.xlsx (16.6 KB) Sample.pbix (154.1 KB)

Is there a dimension (lookup) table for Tasks? I believe the end goal is:image

So my initial thinking here is get the Tasks on rows Date as a slicer to get the initial context. Then from there we can see which task are and aren’t there and all of that. But want to be sure this first step is squared away before diving in.



Thanks for your response.

Correct that is the end goal.

I’ve re uploaded the files again.

data.xlsx (17.9 KB) Sample.pbix (164.8 KB)

Is the 3/5/10 working days from the Created Date?



Yes mate.

Here’s a start.

In Power Query:

  • Parameter for the excel data file location, so be sure to change that to see all the steps taken in power query
  • Created DimTaskStatus, DimTaskName tables.These will be your lookup table

Putting Task Name (From Dim TaskName) and Date from Dates in the matrix. I used this function

Total Working Days from Created to Completed = 
/* Variable to set the Completed date to either what is in the table, or if there is no value use today's date*/
VAR __CompletedDate=
IF( ISBLANK([Task Completed Date]),TODAY(),[Task Completed Date])

/* This tells us the created date, our starting point to count how many days have elapsed*/
VAR __CreatedDate=
CALCULATE( MAX( AcquisitionsThreads[TaskCreatedDate]))

    COUNTROWS(AcquisitionsThreads),                                   --Checks to see if there is data, else would produce a value for every date
        DISTINCTCOUNT(AcquisitionsThreads[TaskID]),                   --We start by wanting to count the # of unique tasks
            DATESBETWEEN(Dates[Date],__CreatedDate, __CompletedDate), --Filter using dates between, which is why used the variables aboe
        Dates[IsworkingDay] = 1,                                      --Then only want to use working days, not every available date
        DimTaskStatus[TaskStatus]="Unactioned"                        --Then also only interested in Unactioned tasks

Exception Report.pbix (150.4 KB)

I dont think it’s 100% what you were looking for, but I think it should provide a good start. Let me know what you think




Thank you for this. Certainly, this is a good start.

Just so i understand, what is the need for those dimension tables? Can they just not be directly referenced in the measure from the original table?

If those dimensions tables are to be used as slicers, can they not be used directly from the original table?

Just want to understand how this fits in overall and the benefit.


In this small data model you could probably get away with it. But it always best practive to breakout your tables into Fact (where the aggregations comes from) and Dimension( where the filters would come from). There are a few reasons:

  1. This will help create a star schema, which DAX is optimized for
  2. This will help keep things organized
  3. Your Dimension tables would probably have more than just one value ( like Task A in this situation). It would have something like Task A, Task Name, Task Subname, etc. Then you can slice by any value in the dimension
  4. When using FILTER in a function, FILTER is an iterator and being an interator you never want to iterate your bigger fact table. You want to iterate the smaller dimension table and then “Send” that filter to your fact tables and then your aggregator will create a final figure based on that.

I’m, sure there is more but that’s the general idea. Hope that helps!


@Nick_M Apologies for the radio silence.

Thank you for your feedback. I was able to get it working with your suggestions.