Forum Discussion

eamonnde's avatar
eamonnde
Frequent Visitor
8 years ago
Solved

Count and Sum Slicer error

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

Need Help
  • eamonnde's avatar
    eamonnde
    8 years ago

    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))

2 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    Hi eamonnde,

     

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

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

     

    Regards

    • eamonnde's avatar
      eamonnde
      Frequent Visitor
      8 years ago

      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))