topic Re: Do calculation on different granularity and show results in different Granularity in DAX Commands and Tips

Do calculation on different granularity and show results in different Granularity

Anonymous — Sun, 19 Sep 2021 06:37:04 GMT

Hi Guys,

I need support on below calculation.

I need to show difference between Downtime_start and Downtime_end for Each ID. But the calculation should happen based on below conditions.

- Under one ID you will have different downtime start times,
- for Each Start time you have different modified times

So you need to do the calculation for unique Downtime_start and you need to consider the Downtime_start with Highest Modified time and for each unique Downtime_start you need to calculate the difference between start and end time and show the total in ID level.

Hope the requirement is clear.

This is the sample data.

PBI file link https://drive.google.com/file/d/1rzL-rMAly6YHCmOwsHdXfSKa9GdR7hLQ/view?usp=sharing

Thank you very much in Advance.

Modified time	ID	DonwTime_Start	DownTime_End
15/09/2021 6:07:14 AM	75	12/09/2021 6:07 AM	13/09/2021 6:11 AM
15/09/2021 6:08:45 AM	75	12/09/2021 6:07 AM	15/09/2021 6:11:48 AM
15/09/2021 6:12:15 AM	75	01/09/2021 6:11 AM	13/09/2021 6:11 AM
15/09/2021 6:14:00 AM	75	01/09/2021 6:11 AM	15/09/2021 6:11:48 AM
15/09/2021 6:17:10 AM	75	10/09/2021 6:16 AM	15/09/2021 6:16:21 AM
15/09/2021 6:17 :15 AM	75	10/09/2021 6:16 AM	15/09/2021 6:16:21 AM

This is my current formula which gives me the correct result at Start date level in the report. But when I view the result in ID level this is not giving me the correct result.

Total Downtime =

VAR _max_modified_date =

CALCULATE(

MAX ( Issue_Tracker[Modified] ),

ALL(Issue_Tracker),

VALUES(Issue_Tracker[DonwTime_Start])

)

VAR _selected_id =

SELECTEDVALUE (Issue_Tracker[ID])

VAR _start_time =

CALCULATE (

SELECTEDVALUE ( Issue_Tracker[DonwTime_Start] ),

FILTER (

Issue_Tracker,

Issue_Tracker[Modified] = _max_modified_date &&

Issue_Tracker[ID] = _selected_id

)

VAR _end_time =

CALCULATE (

SELECTEDVALUE ( Issue_Tracker[DownTime_End] ),

FILTER (

Issue_Tracker,

Issue_Tracker[Modified] = _max_modified_date &&

Issue_Tracker[ID] = _selected_id

)

VAR _result =

DATEDIFF ( _start_time, _end_time, DAY )

RETURN

_result

Re: Do calculation on different granularity and show results in different Granularity

Anonymous — Sun, 19 Sep 2021 05:40:36 GMT

Greg_Deckler
@Daxer-almighty
@daxer-almighty

Re: Do calculation on different granularity and show results in different Granularity

Anonymous — Sun, 19 Sep 2021 05:41:18 GMT

Anonymous daxer-almighty

Re: Do calculation on different granularity and show results in different Granularity

Anonymous — Sun, 19 Sep 2021 05:45:14 GMT

VahidDM

Re: Do calculation on different granularity and show results in different Granularity

Anonymous — Sun, 19 Sep 2021 05:51:03 GMT

ImkeF powerbiblogic Asalk mussaenda parry2k jdbuchanan71

Re: Do calculation on different granularity and show results in different Granularity

jdbuchanan71 — Sun, 19 Sep 2021 07:16:29 GMT

Anonymous

I think something like this will give you what you are looking for.

Duration = VAR _Incidents = SUMMARIZE ( 'Table', 'Table'[ID], 'Table'[DonwTime_Start] ) VAR _AddModified = ADDCOLUMNS ( _Incidents, "@MaxMod", CALCULATE ( MAX ( 'Table'[Modified time] ) ) ) VAR _AddEnd = ADDCOLUMNS ( _AddModified, "@DTEnd", CALCULATE ( MAX ( 'Table'[DownTime_End] ), FILTER('Table', 'Table'[Modified time] = [@MaxMod] ) ) ) RETURN SUMX ( _AddEnd, DATEDIFF ( [DonwTime_Start], [@DTEnd], DAY ) )

Although, could you ignore the modified time and just use the max Downtime End for each unique Downtime Start? That gives me the same answer.

Duration Ignore Modified = VAR _Incidents = SUMMARIZE ( 'Table', 'Table'[ID], 'Table'[DonwTime_Start] ) VAR _AddEnd = ADDCOLUMNS ( _Incidents, "@DTEnd", CALCULATE ( MAX ( 'Table'[DownTime_End] ) ) ) RETURN SUMX ( _AddEnd, DATEDIFF ( [DonwTime_Start], [@DTEnd], DAY ) )

Re: Do calculation on different granularity and show results in different Granularity

Fowmy — Sun, 19 Sep 2021 07:25:41 GMT

Anonymous

I created a new measure:

Total Downtime Period = VAR __RESULT = SUMX( SUMMARIZE(Issue_Tracker, Issue_Tracker[ID] , Issue_Tracker[DonwTime_Start]), VAR __MAXMOD = CALCULATE( MAX(Issue_Tracker[Modified time])) VAR __ENDTIME = CALCULATE( MAX(Issue_Tracker[DownTime_End]) , Issue_Tracker[Modified time] = __MAXMOD ) RETURN DATEDIFF( Issue_Tracker[DonwTime_Start] , __ENDTIME , DAY ) ) RETURN __RESULT

Re: Do calculation on different granularity and show results in different Granularity

Anonymous — Sun, 19 Sep 2021 07:29:06 GMT

Looks like this is giving the correct result I will verify and let you know. Thank you very much for the support jdbuchanan71

Re: Do calculation on different granularity and show results in different Granularity

Anonymous — Sun, 19 Sep 2021 07:44:30 GMT

Thank you very much Fowmy Thank you very much for the support.

Re: Do calculation on different granularity and show results in different Granularity

Anonymous — Sun, 19 Sep 2021 07:45:48 GMT

Thank you very much jdbuchanan71