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

 Ok, so I implemented your recomended DAX code, and it appears to work, in most situations, but i'm getting some odd results of very large negative numbers, and I can't determine the cause.  I mocked up a report using one month of my data, and I've attached the PBIX file...an ideas on where to start? 

 

 

Guess I can't attach files...here is a link to my file in Onedrive

https://1drv.ms/u/s!AswWQQBlp11ZvN1P6rRn-dCyWu7dbw?e=9Hho0X 

MFelix's avatar
MFelix
Icon for Super User rankSuper User
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

    Amazing.  Thank you for your help.  This looks to be exactly the solution to my problem.  I really need to level up my skills with DAX apparently.

     

     

    • 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 ,

         

        This measure can be adapter to do a SUMX (aggregator formula) based on the date for example, only question here is that you need to adapt it to each of the categorizations you need, so if it's by day or by machine or by another group.

         

        I will try and give you some pointer tomorrow ok?