Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Slicer after Measure

 

Hi,

I have a Tabell, that look like this

Employee

  1. [Employee ID]
  2. [Start Date]
  3. [End Date] (Blank if still active)
  4. [Contract Type] 

To count, how many Employee I had each month, I create a first a Tabell that contains data  and in the employee-Tabell I crat a measure

Count of Active Employee =

VAR endOfPeriod =MAX ( 'Date'[Date] )

 

VAR startOfPeriod = MIN( 'Date'[Date] )

RETURN

    CALCULATE (

        COUNTROWS ( tabExterne );

        FILTER (

            ALL(tabExterne);

               ( tabExterne[Start] <= endOfPeriod

                         && tabExterne[Ende] >= startOfPeriod)

        )

)

 

Now I want to crat a Matrix, so that I can see how many Employee I had for each Contact-Type, and this is where I need your help.

Because I try to create a Slicer based on the Contract-Typs and this didn’t work, so I hope you can help me.

Need Help
Tips and Tricks
  • v-piga-msft's avatar
    v-piga-msft
    8 years ago

    Hi Anonymous,

     

    For your scenario, I'm afraid that the cause is the ALL function in your measure.

     

    If you use the All function, it returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. This function is useful for clearing filters and creating calculations on all the rows in a table.

     

    You could try this:

     

    Count of Active Employee =
VAR endOfPeriod =
    MAX ( 'Date'[Date] )
VAR startOfPeriod =
    MIN ( 'Date'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( tabExterne );
        FILTER (
            tabExterne;
            ( tabExterne[Start] <= endOfPeriod
                && tabExterne[Ende] >= startOfPeriod )
        )
    )

    If you still need help, please share some data sample and your desired output.

     

    Best Regards,

    Cherry

3 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    8 years ago

    Hi Anonymous,

     

    For your scenario, I'm afraid that the cause is the ALL function in your measure.

     

    If you use the All function, it returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. This function is useful for clearing filters and creating calculations on all the rows in a table.

     

    You could try this:

     

    Count of Active Employee =
VAR endOfPeriod =
    MAX ( 'Date'[Date] )
VAR startOfPeriod =
    MIN ( 'Date'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( tabExterne );
        FILTER (
            tabExterne;
            ( tabExterne[Start] <= endOfPeriod
                && tabExterne[Ende] >= startOfPeriod )
        )
    )

    If you still need help, please share some data sample and your desired output.

     

    Best Regards,

    Cherry

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi v-piga-msft,

      Thank you, i will check if it working, but after a quick test I would say yes.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    This should be doable.  Share a dataset that can be pasted in MS Excel.