Help Needed: Creating Dashboard and Detail Report with Pivoted Statuses

[manoj_0911]

Hi everyone,

I’m working on building an Agent Status dashboard and detail report in Power BI. My data source is an MS SQL Server view, which includes the following fields:

- `Date`
- `Name`
- `Primary Status` (e.g., Available, Busy, Break) and its `Duration`
- `Secondary Status` (e.g., specific reasons for each Primary Status) and its `Duration`

I've attached a sample of the query result as spreadsheet/image (with confidential data removed) to illustrate the structure.

https://docs.google.com/spreadsheets/d/1Qf-frye78E4iyIJACWc-szxAkNxEi3-k/edit?usp=sharing&ouid=10765...

Requirements:
For the **detail report**, I need the data to be displayed in the following format:

- `Date`
- `Name`
- A separate column for each Primary Status with its total duration (in `dd:hh:mm:ss` format)
- A separate column for each Secondary Status with its total duration (in `dd:hh:mm:ss` format)

Challenge:
My database admin suggested that I should **pivot** the `Primary Status` and `Secondary Status` fields so that each unique status appears as a separate column with its aggregated duration.

But I was able to pivot only Primary Status using duration and for Secondary Status duration was not available for pivoting.

[manoj_0911]

Thanks a lot for your reply, Let me try this and get back to you, meanwhile my db admin says the statuses will be dynamically generated , some new statuses could be added and some could be deleted , in that case what to do 

[MFelix]

If you look at the report I send I use the original table to create the status table so if you addnewstatus everything will be updated. 

I'm doing remove duplicate for the columns and then aplending them

[MFelix · Accepted solution]

Hi @manoj_0911 ,

Create a table with both the Status modes:

Now add the following measure:

Duration Total = 

VAR _Durationvalue = 
        SWITCH(
			SELECTEDVALUE('Status'[Name]),
			"Primary", CALCULATE(
				SUM(Data[STATUS_DURATION]),
				Data[PRIMARY_STATUS] IN VALUES('Status'[Status])
			),
			"Secondary", CALCULATE(
				SUM(Data[STATUS_DURATION]),
				Data[SECONDARY_STATUS] IN VALUES('Status'[Status])
			)
		)
Return

_Durationvalue

Now setup a matrix with the following setup:

• Rows
  • Date
  • Name (Person)
• Columns
  • Name (Status)
  • Status
• Values
  • Duration Total

Final result:

If you want to format as date use this measure assume the values are in seconds:

Duration Total = 

VAR _Durationvalue = 
        SWITCH(
			SELECTEDVALUE('Status'[Name]),
			"Primary", CALCULATE(
				SUM(Data[STATUS_DURATION]),
				Data[PRIMARY_STATUS] IN VALUES('Status'[Status])
			),
			"Secondary", CALCULATE(
				SUM(Data[STATUS_DURATION]),
				Data[SECONDARY_STATUS] IN VALUES('Status'[Status])
			)
		)
Return

// Duration formatting 
// * @konstatinos 1/25/2016
// * Given a number of seconds, returns a format of "hh:mm:ss"
//
// We start with a duration in number of seconds
VAR Duration = _Durationvalue
// There are 3,600 seconds in an hour
VAR Hours =
    INT ( Duration / 3600)
// There are 60 seconds in a minute
VAR Minutes =
    INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60)
// Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours 
VAR Seconds =
    ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number
// These intermediate variables ensure that we have leading zero's concatenated onto single digits
// Hours with leading zeros
VAR H =
    IF ( LEN ( Hours ) = 1, 
        CONCATENATE ( "0", Hours ),
        CONCATENATE ( "", Hours )
      )
// Minutes with leading zeros
VAR M =
    IF (
        LEN ( Minutes ) = 1,
        CONCATENATE ( "0", Minutes ),
        CONCATENATE ( "", Minutes )
    )
// Seconds with leading zeros
VAR S =
    IF (
        LEN ( Seconds ) = 1,
        CONCATENATE ( "0", Seconds ),
        CONCATENATE ( "", Seconds )
    )
// Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss"
RETURN
   IF(_Durationvalue <> BLANK(), CONCATENATE (
        H,
        CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) )
    ))

This formatting code was reused from other user.

Final resul:

See PBIX file attach.