Forum Discussion
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.
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.
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.
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?
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?
Great! Absolutely no rush at all, I appreciate it!
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.