cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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" (removed) and the time it went back "UP" (returned to service).

 

What I want to be able to do is determine based on this data overall serviceability rates for all the equipment in a given period of time, and I can't seem to wrap my head around the problem to get what I need.  

 

I know the total pieces of equipment available, but it might not be consistent on any given day.  (New equipment added, or old equipment removed/obsoleted etc.)

 

The Up/Down records look like this essentially:

EQUIP_ID, EVENT_ID, DOWN_START (Date/Time field), DOWN_STOP (Date/Time field), DOWN_DURATION (minutes)

 

The DOWN_DURATION could sometimes span multiple days.  Also, the quipment is expected to be UP more than it's DOWN, and it's certainly not down every day. 

 

Basically, what I want to do is use this data to create a table like this: 

 

DATE, EQUIP_ID, SERVICEABLE_TIME

 

where DATE is a given calendar date covering every date in a date range that could be filtered with a slicer, EQUIP_ID is the unique ID of the piece of equipment, and SERVICEABLE_TIME is the number of minutes the equipment was available and serviceable on that day.

 

So, I guess my issue here is where to even start with this.  Me and DAX don't get along very well, so I am really up against a wall on this one. 

 

My other thought, based on everyone's expertise here, is this even something that can be handled with DAX?  Or should I move this transformation further back in my stack and do it serverside in Javascript before it gets sent out over the API to PowerBI? 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User V
Super User V

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

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.

MFelix_0-1601547798420.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

10 REPLIES 10
Highlighted
Super User V
Super User V

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

Hi @elietech ,

 

Looking at your information this is possible however you need to give some additional information on your Model.

 

I assume you have and equiment table that is related with your down. Create a calendar table disconnected for your filter and the add the following measure:

Measure = 
VAR Date_Selection =
    MAX ( 'calendar'[Date] )
VAR Date_Selection_Next = Date_Selection + 1
VAR temp_table =
    FILTER (
        ADDCOLUMNS (
            Up_Down;
            "Start";
                DATE ( YEAR ( Up_Down[Down_start] ); MONTH ( Up_Down[Down_start] ); DAY ( Up_Down[Down_start] ) );
            "End";
                DATE ( YEAR ( Up_Down[Down_Stop] ); MONTH ( Up_Down[Down_Stop] ); DAY ( Up_Down[Down_Stop] ) )
        );
        [Start] <= Date_Selection
            && [End] >= Date_Selection
    )
VAR DateStart =
    IF (
        MINX ( temp_table; Up_Down[Down_start] ) <= Date_Selection;
        Date_Selection;
        MINX ( temp_table; Up_Down[Down_start] )
    )
VAR DateEnd =
    IF (
        MAXX ( temp_table; Up_Down[Down_Stop] ) >= Date_Selection_Next;
        Date_Selection_Next;
        MAXX ( temp_table; Up_Down[Down_Stop] )
    )
VAR Same_day_selection =
    IF (
        MAXX ( temp_table; [Start] ) = MINX ( temp_table; [End] )
            && MINX ( temp_table; Up_Down[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
                - SUM ( Up_Down[Down_duration] ) * 1440
        )
    )

 

Check final result in PBIX file attach.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Helper II
Helper II

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

Sorry for the delayed response...this looks very promising....one quick question before I start digging into it....my model already contains a "date/calendar" table...would I need to make another one?  Or can I use the existing one?   And yes, I do have an "Equipment" table that is relatable to the Up/Down events data....

 

Can't wait to give this a try...definately a level of DAX beyond what I am normally able to dream up on my own 🙂  I'm constantly amazed by the help and support I can find on this community. 

 

 

Highlighted
Super User V
Super User V

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

Hi @elietech,

 

I use a unrelated table but the use of this other table depends on the relationship you have on your model. 

 

How does the date table relates with the other tables in the model? 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Helper II
Helper II

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

 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? 

 

elietech_0-1601492186998.png

 

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

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

Highlighted
Super User V
Super User V

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

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.

MFelix_0-1601547798420.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

Highlighted
Helper II
Helper II

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

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.

 

 

Highlighted
Helper II
Helper II

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

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?  

Highlighted
Super User V
Super User V

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

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?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Helper II
Helper II

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

Great!  Absolutely no rush at all, I appreciate it!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors