DAX Measure Exception Report

[adsa]

Hi,

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 =
CALCULATE (
COUNT ( AcquistionsThreads[TaskID] ),
FILTER (
    AcquistionsThreads,
    AcquistionsThreads[TaskStatus] <> "Complete"
        && AcquistionsThreads[TaskStatus] <> "Aborted"
)
)

Task Days Lapsed = 
VAR StartDate =
    MIN ( AcquistionsThreads[TaskCreatedDate] )
VAR DueDate =
    TODAY ()
RETURN
    CALCULATE (
        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] )
RETURN
    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 :

Thanks

[Nick_M]

@adsa
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.

[adsa]

@Nick_M

Thanks for your quick response.

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

[Nick_M]

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

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.

[adsa]

@Nick_M

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)

[Nick_M]

@adsa

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

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

[adsa]

@Nick_M

Yes mate.

[Nick_M]

@adsa,
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]))
RETURN

IF(
    COUNTROWS(AcquisitionsThreads),                                   --Checks to see if there is data, else would produce a value for every date
    CALCULATE(
        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

-Nick

[adsa]

@Nick_M

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.

Thanks

[Nick_M]

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!

[adsa]

@Nick_M Apologies for the radio silence.

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