Forum Discussion

kmcardle's avatar
kmcardle
Icon for Helper I rankHelper I
4 years ago
Solved

The Function SUMX cannot Work with values of type Boolean

Here is my Measure.  I cannot figure out what I am doing wrong (Newbie).  I get the above Error and I searched "the" google and was having trouble finding any datetime SUMX examples.  Hoping you more experienced people can help.

 

SRHours =
IF ( ( ( (DATEDIFF (SUMX('SRInsights-SupportCases','SRInsights-SupportCases'[Date/Time Opened] ),TODAY (),DAY ) ) - (DATEDIFF (SUMX ('SRInsights-SupportCases','SRInsights-SupportCases'[First Response Date] ),TODAY (),DAY ) )) = 1 ) && SUMX ('SRInsights-SupportCases','SRInsights-SupportCases'[First Response Date] ) >= SUMX ('SRInsights-SupportCases','SRInsights-SupportCases'[Date/Time Opened].[Date] + TIMEVALUE ( "08:00:00" ) ) && SUMX ('SRInsights-SupportCases','SRInsights-SupportCases'[First Response Date] ) <= SUMX ('SRInsights-SupportCases','SRInsights-SupportCases'[Date/Time Opened].[Date] + TIMEVALUE ( "18:00:00" ) ), ( ( (DATEDIFF (SUMX ( 'SRInsights-SupportCases', 'SRInsights-SupportCases'[Date/Time Opened] ),TODAY (),DAY ) ) - DATEDIFF (SUMX ('SRInsights-SupportCases','SRInsights-SupportCases'[First Response Date] ),TODAY (),DAY ) ) * 10 ) + (DATEDIFF (SUMX ('SRInsights-SupportCases','SRInsights-SupportCases'[First Response Date].[Date] + TIMEVALUE ( "08:00:00" ) ),SUMX ('SRInsights-SupportCases','SRInsights-SupportCases'[First Response Date] ),MINUTE ) ), IF ( ( ( (DATEDIFF (SUMX ( 'SRInsights-SupportCases', 'SRInsights-SupportCases'[Date/Time Opened] ),TODAY (),DAY ) ) - DATEDIFF (SUMX ('SRInsights-SupportCases','SRInsights-SupportCases'[First Response Date] ),TODAY (),DAY ) ) > 1 ) && SUMX ( 'SRInsights-SupportCases', 'SRInsights-SupportCases'[Date/Time Opened] ) >= SUMX ('SRInsights-SupportCases','SRInsights-SupportCases'[Date/Time Opened].[Date] + TIMEVALUE ( "08:00:00" ) ) && SUMX ( 'SRInsights-SupportCases', 'SRInsights-SupportCases'[Date/Time Opened] ) <= SUMX ('SRInsights-SupportCases','SRInsights-SupportCases'[Date/Time Opened].[Date] + TIMEVALUE ( "18:00:00" ) && SUMX ('SRInsights-SupportCases','SRInsights-SupportCases'[First Response Date] ) >= SUMX ('SRInsights-SupportCases','SRInsights-SupportCases'[First Response Date].[Date] + TIMEVALUE ( "08:00:00" ) ) && SUMX ('SRInsights-SupportCases','SRInsights-SupportCases'[First Response Date] ) <= SUMX ('SRInsights-SupportCases','SRInsights-SupportCases'[Date/Time Opened].[Date] + TIMEVALUE ( "18:00:00" ) ) ), ( (DATEDIFF (SUMX ( 'SRInsights-SupportCases', 'SRInsights-SupportCases'[Date/Time Opened] ),TODAY (),DAY ) - DATEDIFF (SUMX ('SRInsights-SupportCases','SRInsights-SupportCases'[First Response Date] ),TODAY (),DAY ) * 10 ) + (DATEDIFF (SUMX ( 'SRInsights-SupportCases', 'SRInsights-SupportCases'[Date/Time Opened] ),SUMX ('SRInsights-SupportCases','SRInsights-SupportCases'[Date/Time Opened].[Date] + TIMEVALUE ( "18:00:00" ) ),MINUTE ) + (DATEDIFF (SUMX ('SRInsights-SupportCases','SRInsights-SupportCases'[First Response Date].[Date] + TIMEVALUE ( "08:00:00" ) ),SUMX ('SRInsights-SupportCases','SRInsights-SupportCases'[First Response Date] ),MINUTE ) ) ) ), ( (DATEDIFF (SUMX ( 'SRInsights-SupportCases', 'SRInsights-SupportCases'[Date/Time Opened] ),TODAY (),DAY ) - DATEDIFF (SUMX ('SRInsights-SupportCases','SRInsights-SupportCases'[First Response Date] ),TODAY (),DAY ) * 10)
+ (DATEDIFF (SUMX ('SRInsights-SupportCases','SRInsights-SupportCases'[First Response Date].[Date] + TIMEVALUE ( "08:00:00" ) ),SUMX ('SRInsights-SupportCases','SRInsights-SupportCases'[First Response Date] ),MINUTE) ) ) ))
 
I know this is complex but I kept running into Cyclic Redundancy issues with combining 3 measures.  Again, newbie issues I'm sure, but I am running out of time to get this figured out.
 
Thanks in advance for trhe hlep.
Kevin
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi kmcardle ,

     

    Has your problem been solved? If it is solved, please mark a reply which is helpful to you.

     

    I found that some of the parentheses in your formula were in the wrong place, causing the second argument of the SUMX function to return a boolean value. I have formatted your formula so that it now returns values and you can make changes to this formula to implement your needs.

     

    SR_Hours_Measure = 
VAR S_FRD = SUMX ( 'SRInsights-SupportCases', 'SRInsights-SupportCases'[First Response Date] )
VAR S_DO = SUMX ( 'SRInsights-SupportCases', 'SRInsights-SupportCases'[Date/Time Opened] )
VAR Diff_DO = DATEDIFF ( S_DO, TODAY (), DAY )
VAR Diff_FRD = DATEDIFF ( S_FRD, TODAY (), DAY )
VAR S_DOT1 =
    SUMX (
        'SRInsights-SupportCases',
        'SRInsights-SupportCases'[Date/Time Opened].[Date] + TIMEVALUE ( "08:00:00" )
    )
VAR S_DOT2 =
    SUMX (
        'SRInsights-SupportCases',
        'SRInsights-SupportCases'[Date/Time Opened].[Date] + TIMEVALUE ( "18:00:00" )
    )
VAR S_FRDT =
    SUMX (
        'SRInsights-SupportCases',
        'SRInsights-SupportCases'[First Response Date].[Date] + TIMEVALUE ( "08:00:00" )
    )
RETURN
    IF (
        ( Diff_DO  - Diff_FRD = 1 ) && S_FRD >= S_DOT1 && S_FRD <= S_DOT2,
        Diff_DO  - Diff_FRD * 10 + DATEDIFF ( S_FRDT, S_FRD, MINUTE ),
        IF (
            ( Diff_DO  - Diff_FRD > 1 ) && S_DO >= S_DOT1 && S_DO <= S_DOT2 && S_FRD >= S_FRDT && S_FRD <= S_DOT2,
            ( Diff_DO  - Diff_FRD ) * 10 + DATEDIFF ( S_DO, S_DOT2, MINUTE ) + DATEDIFF ( S_FRDT, S_FRD, MINUTE ),
            Diff_DO  - Diff_FRD * 10 + DATEDIFF ( S_FRDT, S_FRD, MINUTE )
        )
    )

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

7 Replies