How to perform calculations on this measure

[Qotsa]

Hi,

I have this measure: 

Rest Period Decimal Hrs =
VAR thisdate =
MIN ( 'powerbi114 view_finance'[Date] )
VAR thisfirst =
MIN ( [Time of First Call] )
VAR prevdate =
CALCULATE (
MAX ( 'powerbi114 view_finance'[Date] ),
ALL ( 'powerbi114 view_finance' ),
VALUES ( 'powerbi114 view_finance'[carer_id] ),
'powerbi114 view_finance'[Date] < thisdate
)
VAR prevlast =
CALCULATE (
MAX ( 'powerbi114 view_finance'[Time of Last Call] ),
ALL ( 'powerbi114 view_finance' ),
VALUES ( 'powerbi114 view_finance'[carer_id] ),
'powerbi114 view_finance'[Date] = prevdate
)
RETURN
IF (
NOT ( ISBLANK ( prevdate ) ),
24
* FIXED ( ( thisdate + thisfirst ) - ( prevdate + prevlast ), 3 )
)

It returns the time between the Latest Finish Time from the previous date and the Earliest Start Time of the next date for each carer_id.

The issue I am having is how to perform calculations on the measure.

For example, I want a sum of all values < 11 but sumx does not work with boolean values.

Less than 11 = SUMX('powerbi114 view_finance',[Daily Rest Decimal Hrs] < 11)

[Qotsa]

Thks again. Can you explain what is happening here?

SUMX(
    FILTER(
        SUMMARIZE(        
            'powerbi114 view_finance',
            'powerbi114 view_finance'[carer_id],
            'powerbi114 view_finance'[Date]
       )

[SpartaBI]

SUMMARIZE(        
            'powerbi114 view_finance',
            'powerbi114 view_finance'[carer_id],
            'powerbi114 view_finance'[Date]
       )

This is the distinct combination of these 2 columns.
Check out:
https://dax.guide/summarizea


 

[Qotsa]

Ok. Thanks very much.

[SpartaBI]

@Qotsa my pleasure 🙂
Don't forget to check out my showcase report - really, got some high level stuff there. Sure you will find there a lot of cool ideas. 
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up over there if you liked it 🙂

[SpartaBI]

@Qotsa I think you are trying to do:
Less than 11 = SUMX(FILTER('powerbi114 view_finance',[Daily Rest Decimal Hrs] < 11),[Daily Rest Decimal Hrs])
 

      

Showcase Report – Contoso By SpartaBI

[Qotsa]

@SpartaBI Thks. I'm not getting desired results. Should return a sum of 2.

[SpartaBI]

@Qotsa oh, so do this:) :
Less than 11 = SUMX(FILTER('powerbi114 view_finance',[Daily Rest Decimal Hrs] < 11),1)

      

Showcase Report – Contoso By SpartaBI

[Qotsa]

It's getting closer.

[SpartaBI · Accepted solution]

@Qotsa try:

Less than 11 = 
SUMX(
    FILTER(
        SUMMARIZE(        
            'powerbi114 view_finance',
            'powerbi114 view_finance'[carer_id],
            'powerbi114 view_finance'[Date]
       )
        [Daily Rest Decimal Hrs] < 11
    )
    ,1
)

      

Showcase Report – Contoso By SpartaBI

[Qotsa]

That's fantastic. I'm getting the correct results for <11.

When I change it tho to =, it doesn't return the desired result. Any idea why?

[SpartaBI]

@Qotsa I guess it's not exactly 32.26 🙂
Try to verify by doing 

Less than 11 = 
SUMX(
    FILTER(
        SUMMARIZE(        
            'powerbi114 view_finance',
            'powerbi114 view_finance'[carer_id],
            'powerbi114 view_finance'[Date]
       )
        [Daily Rest Decimal Hrs] > 32 && [Daily Rest Decimal Hrs] < 33 
    )
    ,1
)

      

Showcase Report – Contoso By SpartaBI

[Qotsa]

Yep, that gives correct result.

[SpartaBI]

@Qotsa my pleasure 🙂
Please don't forget to accept the previous message as a solution for community visibility.
P.S. Check out my showcase report - got some high level stuff there. Sure you will find there a lot of cool ideas. 
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up over there if you liked it 🙂