Incorrect Duration Calculation Due to GROUP and TEAM Associations in Power BI

[manoj_0911]

Hello Power BI Experts,

SOME DAX I CREATED FOR FIRST REQUIREMENT

Available_Seconds =

DIVIDE(SUM(AGENT_STATUS_SUBHOUR_V[AVAILABLE]), DISTINCTCOUNT(AGENT_STATUS_SUBHOUR_V[GROUP_NAME]), 0)

------------------------------------------------------------------------------------------------------------------------------------------

AVAILABLE DURATION IN DETAIL TABLE =

VAR RowLevel_Available =
DIVIDE(
SUM(AGENT_STATUS_SUBHOUR_V[AVAILABLE]),
DISTINCTCOUNT(AGENT_STATUS_SUBHOUR_V[GROUP_NAME]),
0
)

VAR TotalLevel_Available =
SUMX(
SUMMARIZE(
AGENT_STATUS_SUBHOUR_V,
AGENT_STATUS_SUBHOUR_V[USER_ID], -- Calculate per agent
"AgentAvailable", SUM(AGENT_STATUS_SUBHOUR_V[AVAILABLE]),
"AgentGroupCount", DISTINCTCOUNT(AGENT_STATUS_SUBHOUR_V[GROUP_NAME])
),
DIVIDE([AgentAvailable], [AgentGroupCount], 0)
)

VAR FinalSeconds =
IF(
ISINSCOPE(AGENT_STATUS_SUBHOUR_V[USER_ID]),
RowLevel_Available, -- Use the row-level calculation when in scope
TotalLevel_Available -- Use the total calculation when out of scope
)

-- Convert Total Seconds to DD:HH:MM:SS Format
VAR Days = INT(FinalSeconds / 86400)
VAR Hours = INT(MOD(FinalSeconds, 86400) / 3600)
VAR Minutes = INT(MOD(FinalSeconds, 3600) / 60)
VAR Seconds = MOD(FinalSeconds, 60)

RETURN
IF(
ISBLANK(FinalSeconds),
BLANK(),
FORMAT(Days, "0") & ":" & FORMAT(Hours, "00") & ":" & FORMAT(Minutes, "00") & ":" & FORMAT(Seconds, "00")
)
-----------------------------------------------------------------------------------------------------------------------------
AVG AVAILABLE TIME IN CARDS =
VAR __total_seconds = SUM(AGENT_STATUS_SUBHOUR_V[AVAILABLE]) // Sum of available seconds
VAR __distinct_groups = DISTINCTCOUNT(AGENT_STATUS_SUBHOUR_V[GROUP_NAME]) // Replace GROUP_COLUMN with the appropriate column name
VAR __average_seconds = DIVIDE(__total_seconds, __distinct_groups, 0) // Divide total seconds by distinct group count

// Convert the result (average seconds) into DD:HH:MM:SS format
VAR __days = INT(__average_seconds / 86400) // Calculate days
VAR __hours = INT(MOD(__average_seconds, 86400) / 3600) // Calculate remaining hours
VAR __minutes = INT(MOD(__average_seconds, 3600) / 60) // Calculate remaining minutes
VAR __seconds = MOD(__average_seconds, 60) // Calculate remaining seconds

// Format the result into DD:HH:MM:SS
VAR __result = FORMAT(__days, "0") & ":" & FORMAT(__hours, "00") & ":" & FORMAT(__minutes, "00") & ":" & FORMAT(__seconds, "00")

// Return the formatted result or BLANK if no data
RETURN
IF(ISBLANK(__average_seconds), BLANK(), __result)
--------------------------------------------------------------------------------------------------------------------------------

DB Admin Suggested:

Passing parameters from Power BI to a query directly instead of relying on DB views.
💡Issue: Power BI (or any BI tool) has limitations when handling parameters like a programming language (Java, .NET). Is my understanding correct?

My Manager Suggested:

Joining dimension tables 

This is a production issue, and I need to resolve it ASAP. Your help would be greatly appreciated! 🙏

[shafiz_p]

Hi @manoj_0911 

It is better to state your problem in clear and concise manner, this way it would be helpful to understand easily. What is the problem and what would be your desired output , if prossible , showing dummy output.

However, what I understood that, you want to evaluate duration, but want to consider the impact of both team and group while consider removing duplicates. You could try and update your code with below and check result that it evaluates correctly.

Available_Seconds = 

DIVIDE(
    SUM(AGENT_STATUS_SUBHOUR_V[AVAILABLE]),
    DISTINCTCOUNT(AGENT_STATUS_SUBHOUR_V[GROUP_NAME]) * DISTINCTCOUNT(AGENT_STATUS_SUBHOUR_V[TEAM]),
    0
)

AVAILABLE DURATION IN DETAIL TABLE = 
VAR RowLevel_Available =
    DIVIDE(
        SUM(AGENT_STATUS_SUBHOUR_V[AVAILABLE]),
        DISTINCTCOUNT(AGENT_STATUS_SUBHOUR_V[GROUP_NAME]) * DISTINCTCOUNT(AGENT_STATUS_SUBHOUR_V[TEAM_NAME]),
        0
    )

VAR TotalLevel_Available =
    SUMX(
        SUMMARIZE(
            AGENT_STATUS_SUBHOUR_V,
            AGENT_STATUS_SUBHOUR_V[USER_ID], -- Calculate per agent
            "AgentAvailable", SUM(AGENT_STATUS_SUBHOUR_V[AVAILABLE]),
            "AgentGroupCount", DISTINCTCOUNT(AGENT_STATUS_SUBHOUR_V[GROUP_NAME]),
            "AgentTeamCount", DISTINCTCOUNT(AGENT_STATUS_SUBHOUR_V[TEAM_NAME])
        ),
        DIVIDE([AgentAvailable], [AgentGroupCount] * [AgentTeamCount], 0)
    )

VAR FinalSeconds =
    IF(
        ISINSCOPE(AGENT_STATUS_SUBHOUR_V[USER_ID]),
        RowLevel_Available, -- Use the row-level calculation when in scope
        TotalLevel_Available -- Use the total calculation when out of scope
    )

-- Convert Total Seconds to DD:HH:MM:SS Format
VAR Days = INT(FinalSeconds / 86400)
VAR Hours = INT(MOD(FinalSeconds, 86400) / 3600)
VAR Minutes = INT(MOD(FinalSeconds, 3600) / 60)
VAR Seconds = MOD(FinalSeconds, 60)

RETURN
    IF(
        ISBLANK(FinalSeconds),
        BLANK(),
        FORMAT(Days, "0") & ":" & FORMAT(Hours, "00") & ":" & FORMAT(Minutes, "00") & ":" & FORMAT(Seconds, "00")
    )

AVG AVAILABLE TIME IN CARDS = 
VAR __total_seconds = SUM(AGENT_STATUS_SUBHOUR_V[AVAILABLE]) // Sum of available seconds
VAR __distinct_groups = DISTINCTCOUNT(AGENT_STATUS_SUBHOUR_V[GROUP_NAME]) // Distinct group count
VAR __distinct_teams = DISTINCTCOUNT(AGENT_STATUS_SUBHOUR_V[TEAM_NAME]) // Distinct team count
VAR __average_seconds = DIVIDE(__total_seconds, __distinct_groups * __distinct_teams, 0) // Divide total seconds by distinct group and team count

// Convert the result (average seconds) into DD:HH:MM:SS format
VAR __days = INT(__average_seconds / 86400) // Calculate days
VAR __hours = INT(MOD(__average_seconds, 86400) / 3600) // Calculate remaining hours
VAR __minutes = INT(MOD(__average_seconds, 3600) / 60) // Calculate remaining minutes
VAR __seconds = MOD(__average_seconds, 60) // Calculate remaining seconds

// Format the result into DD:HH:MM:SS
VAR __result = FORMAT(__days, "0") & ":" & FORMAT(__hours, "00") & ":" & FORMAT(__minutes, "00") & ":" & FORMAT(__seconds, "00")

// Return the formatted result or BLANK if no data
RETURN
    IF(ISBLANK(__average_seconds), BLANK(), __result)

By multiplying DISTINCTCOUNT(AGENT_STATUS_SUBHOUR_V[GROUP_NAME]) * DISTINCTCOUNT(AGENT_STATUS_SUBHOUR_V[TEAM]), you are accounting for the combined distinct associations of a user with both groups and teams. This ensures that the total duration is divided by the correct number of distinct combinations of groups and teams, preventing overcounting.

Hope this helps!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

[manoj_0911]

Thank you so much for your reply, I used your logic and showed to my db admin he replied like "

Available_Seconds = DIVIDE( SUM(AGENT_STATUS_SUBHOUR_V[AVAILABLE]), DISTINCTCOUNT(AGENT_STATUS_SUBHOUR_V[GROUP_NAME]) * DISTINCTCOUNT(AGENT_STATUS_SUBHOUR_V[TEAM]), 0 )

This may work. But what will happen if we add any kind if this metric in near future... like division.

The permanent solutions is having standalone filters. Meaning, Filters should be populated from  static table and the selected values should be passed to the result set for further filtration.

[v-shex-msft]

Hi @manoj_0911 ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng