Count and Sum Slicer error

[eamonnde]

Hi,

I'm having issues getting a slicer to work with other tables and graphs within a report that I've created, all interactions are set up correctly.

The interactions and slicer work when the measure calculation for value is set up as a count, but not when this measure is changed to a sum.

**************Count**************
ActiveEmployees = COUNTX( 'Table 1',
CALCULATE( COUNTA('Date'[DateKey]),
FILTER( 'Date',
'Date'[DateKey] >= 'Table 1'[CommencedDateKey] && 'Table 1'[DateKey] <= 'Table 1'[EndedDateKey]) ) )

***************SUM***************
ActiveEmployees = SUMX( 'Table 1',
CALCULATE( VALUES('Table 1'[ABC]),
FILTER( 'Date',
'Date'[DateKey] >= 'Table 1'[CommencedDateKey] && 'Date'[DateKey] <= 'Table 1'[EndedDateKey]) ) )

There is also no relationship between the date table and the main table.

Can someone please provide some support with this?

Thanks

[v-ljerr-msft]

Hi @eamonnde,

Could you try using the formula below to see if it works? 

ActiveEmployees =
SUMX (
    'Date',
    CALCULATE (
        SUM ( 'Table 1'[ABC] ),
        FILTER (
            'Table 1',
            'Date'[DateKey] >= 'Table 1'[CommencedDateKey]
                && 'Date'[DateKey] <= 'Table 1'[EndedDateKey]
        )
    )
)

Regards

[eamonnde · Accepted solution]

Thanks @v-ljerr-msft,

Sorry for the slow response, this gave me something close to what i was looking for. I managed to solve the issues using the below equation. 

Thanks again.

ActiveFTE:= SUMX( 'Table 1', 'Table 1'[FTE] * IF( 

        CALCULATE( COUNTA( 'Date'[DateKey]), 

                   FILTER( 'Date',  

                           'Date'[DateKey] >= 'Table 1'[CommencedDateKey] && 'Date'[DateKey] <= 'Table 1'[EndedDateKey]) )  > 0 , 1 , 0))