Forum Discussion

elietech's avatar
elietech
Icon for Helper II rankHelper II
5 years ago
Solved

Transform and summarize data from one table into another...

Ok, here is the problem I am faced with.     I have a data set that records everytime a piece of equipment is removed from service for being unserviceable, and records the time it went "DOWN" (remo...
Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    Hi elietech .

     

    The error was related with the calculation when you have a single day, I already fixed it by changing the formula to:

    measure = 
VAR Date_Selection =
    MAX ( 'Dates'[Date] )
VAR Date_Selection_Next = Date_Selection + 1
VAR temp_table =
    FILTER (
        ADDCOLUMNS (
            UpDown;
            "Start";
                DATE ( YEAR ( UpDown[DOWN_START] ); MONTH ( UpDown[DOWN_START] ); DAY ( UpDown[DOWN_START] ) );
            "End";
                DATE ( YEAR ( UpDown[DOWN_STOP] ); MONTH ( UpDown[DOWN_STOP] ); DAY ( UpDown[DOWN_STOP] ) )
        );
        [Start] <= Date_Selection
            && [End] >= Date_Selection
    )
VAR DateStart =
    IF (
        MINX ( temp_table; UpDown[DOWN_START] ) <= Date_Selection;
        Date_Selection;
        MINX ( temp_table; UpDown[DOWN_START] )
    )
VAR DateEnd =
    IF (
        MAXX ( temp_table; UpDown[DOWN_STOP] ) >= Date_Selection_Next;
        Date_Selection_Next;
        MAXX ( temp_table; UpDown[DOWN_STOP] )
    )
VAR Same_day_selection =
    IF (
        MAXX ( temp_table; [Start] ) = MINX ( temp_table; [End] )
            && MINX ( temp_table; UpDown[DOWN_START] ) >= Date_Selection;
        1
    )
RETURN
    IF (
        DateEnd = BLANK ();
        1440;
        IF (
            Same_day_selection = BLANK ();
            DATEDIFF ( Date_Selection; DateStart; MINUTE )
                + DATEDIFF ( DateEnd; Date_Selection_Next; MINUTE );
            1440
                - CALCULATE (
                    SUM ( UpDown[DOWN_DURATION] );
                    FILTER ( temp_table; UpDown[DOWN_START] = DateStart )
                )
        )
    )

     

    Check result below and attach.

     

elietech's avatar
elietech
Icon for Helper II rankHelper II
5 years ago

Hi MFelix , maybe if I could trouble you with a small followup to this...

 

As I started to integrate this into my existing BI report, I noticed a small limitation with this measure...or maybe its a limitation with me....either way, I was wondering:

 

It seems that this measure works really well, at a super granular level; looking specifically at number of hours available per resource per day.  But it doesn't seem to work if I want to look at something like say, total number of hours available per day for all resources...to try and get an overall sense of the overall serviceability, or by group of resource ( I left out that info from the previous example data I sent, but say each resource belongs to a group, and there are about 5 or 6 different groups total)  

 

Could this measure be adapted to work for these scenarios as well?  Or would a different measure be required?  

MFelix's avatar
MFelix
Icon for Super User rankSuper User
5 years ago

Hi elietech ,

 

As I refered you need to create a different measure based on your context in my case I have made a calculation for the days and ID

 

Measure 2 = IF(ISINSCOPE(Dates[Date]);[measure]; SUMX(Dates;SUMX(ALLSELECTED(UpDown[RES_ID]);[measure])))

 

You need to change the two values in SUMX based on the aggregation you need. Be aware that using the disconnected tables and the SUMX can bring some performance issues depending on the size of your data model.

 

Check PBIX file attach.