The Function SUMX cannot Work with values of type Boolean

[kmcardle]

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

[amitchandak]

@kmcardle , what are you trying here?

I can see date and time in sumx, which I doubt will work

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

for the sum of Duration
https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-usi...
https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-i...

[kmcardle]

Sorry that took forever.  Here is a Screen Shot of the error.  Note that if you look at other measure such as SRDays (i Believe) SUMX is used.  I need to figure out how to upload my PBIX file for you.

[kmcardle]

Here is the link to my PBIX

https://ufile.io/z2ljip33

[kmcardle]

I may have found the problem or at least a workaround.  I will update the Post once I can confirm.

Thank you 

[v-kkf-msft · Accepted solution]

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.

[kmcardle]

Sorry for my slow reply.  Thank you so much for your input and response.  That looks much better than what I had.  

Thank you so much.

[kmcardle]

Thank you for the quick reply.  It might take me a little bit.  I have never tried to clean a dataset coming from a non-excel format before.  I will do my best.

As for you comment about SUMX and DateDiff.  That was the only way I have been able to accomplish creating new measures based on the DateTime data up to now. Perhaps I am doing something wrong in that manor.

Again, thank you.

Kevin