Forum Discussion

igaca's avatar
igaca
Icon for Helper III rankHelper III
9 years ago
Solved

Efficiently Comparing Single Fact Table Subsets to one Another Using DAX

  Often times I run into a scenario where I am working with multiple schedules data sets and need to draw comparisons between them to review progress, over time.   To facilitate this, I typically ...
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-sihou-msft's avatar
    v-sihou-msft
    8 years ago

    igaca

     

    In my opinion, I prefer the approach that merge all files into one fact table. So your table will have file as highest grain, then multiple activities group on file level. There might be data for same activity in multiple files. 

     

    In this scenario, you can create a measure to get the max date of all dates before current schedule start date and group on Activity level. Then you can have current StartDate minus LastStartDate measure to get the Slippage. 

     

    Slippage =
1
    * (
        CALCULATE ( MAX ( Table[StartDate] ), ALLEXCEPT ( Table, Table[ActivityId] ) )
            - CALCULATE (
                MAX ( Table[StartDate] ),
                FILTER ( Table, Table[StartDate] < MAX ( Table[StartDate] ) ),
                ALLEXCEPT ( Table, Table[ActivityId] )
            )
    )

    Regards,

     

     

igaca's avatar
igaca
Icon for Helper III rankHelper III
8 years ago

Hi Simon:

 

Very interesting approach...let me make sure I understand it correctly:

 

First part:   CALCULATE(MAX('ScheduleData'[Start]), ALLEXCEPT('ScheduleData','ScheduleData'[Activity ID]))

 

In short, ALLEXCEPT is used to preclude anything from consideration but the current-filter-context Activity ID, for which a max Start Date is returned (among various schedule starts for that Activ ID). 

 

Second part:    CALCULATE(MAX('ScheduleData'[Start]),FILTER('ScheduleData','ScheduleData'[Start] < MAX('ScheduleData'[Start])), ALLEXCEPT('ScheduleData','ScheduleData'[Activity ID]))

 

This expression is the LastStartDate measure, one that returns the Start Date before the current Start Date.

 

In the integrated expression, we are simply taking the difference between the two and the "1" multiplier is meant for conversion from a date value.

 

Let me know if I am understanding things correctly.  Thanks!

 

 

igaca's avatar
igaca
Icon for Helper III rankHelper III
8 years ago

I like the idea but it's not quite what I'm after.  Looks like it's hard to get around it without using EARLIER because File name has to be taken into account as well, not just the Start Date.  Let's take a closer look:

 

Measures as follows, derived from your approach:

 

Earlier Schedule Start = CALCULATE(MAX('ScheduleData'[Start]), ALLEXCEPT('ScheduleData','ScheduleData'[Activity ID]))

Later Schedule Start = CALCULATE(MAX('ScheduleData'[Start]),FILTER('ScheduleData','ScheduleData'[Start] < MAX('ScheduleData'[Start])), ALLEXCEPT('ScheduleData','ScheduleData'[Activity ID]))

Earlier Schedule Finish = CALCULATE(MAX('ScheduleData'[Finish]), ALLEXCEPT('ScheduleData','ScheduleData'[Activity ID]))

Later Schedule Finish = CALCULATE(MAX('ScheduleData'[Finish]),FILTER('ScheduleData','ScheduleData'[Finish] < MAX('ScheduleData'[Finish])), ALLEXCEPT('ScheduleData','ScheduleData'[Activity ID]))

Start Date Var = (CALCULATE(MAX('ScheduleData'[Start]), ALLEXCEPT('ScheduleData','ScheduleData'[Activity ID]))-CALCULATE(MAX('ScheduleData'[Start]),FILTER('ScheduleData','ScheduleData'[Start] < MAX('ScheduleData'[Start])), ALLEXCEPT('ScheduleData','ScheduleData'[Activity ID])))*1

Finish Data Var = (CALCULATE(MAX('ScheduleData'[Finish]), ALLEXCEPT('ScheduleData','ScheduleData'[Activity ID]))-CALCULATE(MAX('ScheduleData'[Finish]),FILTER('ScheduleData','ScheduleData'[Finish] < MAX('ScheduleData'[Finish])), ALLEXCEPT('ScheduleData','ScheduleData'[Activity ID])))*1

 

 

Results:

 

Test with Activity ID A14170 filtered for:

 

Measure works but is not what I'm after. It works over a single column table of results (Start Date) but the desire is to compute a variance for the previous file (not overall)See notesExcel data for Activ A14170

What I'm really after if the following:  below excerpt is one of a matrix visual with the schedule Data Dates (unique to each file name) across the top as column headers, Activity IDs for rows and the "Start" date column on the values field.  This view shows both the change in the start dates over time, as well as when an activity is present in a particular schedule file or not.  Now rather than showing the dates, I'd like to be able to show "Start Variance to the Previous Schedule" (for instance)