Create measure which excludes the relationship or slicer

[Anonymous]

Below is the screenshot of my report. I have a date hierarchy filter which comes from DimDate table and filters all the other visuals. However I need the below two pie charts to be constant. It should not accept the value from Slicer not only by means of edit interactions, but also it should avoid the relationship between the source table and DimDate table.

1st Pie Chart - Number of employees joined on current month Apr 2021

2nd Pie Chart - Number of employees joined on current month but  previous year Apr 2020

How to do this. Please help.

[v-yetao1-msft · Accepted solution]

Hi @Anonymous ，

Please correct me if I wrongly understood your question.

You said that you need your calculation like the count of employees who were active in the company for current month and last year same month. So we only need to count the end date in the current month . If the current month of last year, the end date is between the first and last day of the current month.

I create two measures to count the number of employees whose end date in current month and current month in previous year .

last year current month = CALCULATE(COUNTA('Table'[Employee Name]),FILTER('Table','Table'[End Date]>=DATE(YEAR(TODAY())-1,MONTH(TODAY()),1) && 'Table'[End Date]<=EOMONTH(DATE(YEAR(TODAY())-1,MONTH(TODAY()),1),0)))

current year current month = CALCULATE(COUNTA('Table'[Employee Name]),FILTER('Table','Table'[End Date]>=DATE(YEAR(TODAY()),MONTH(TODAY()),1)))

The effect is as shown :

If your data does not want to be affected by the slicer and relationship , you can edit interactions in Pie Charts , set None . And then make the relationship between the source table and DimDate table interactive .

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fowmy]

@Anonymous 

You can create two measures as follows, changing the date slicer will not affect the measures.

Apr 2020 Count = 
CALCULATE(
    COUNTROWS(Employees),
    DimDate[Year] = 2020,
    DimDate[Month] = 4
)

Apr 2021 Count = 
CALCULATE(
    COUNTROWS(Employees),
    DimDate[Year] = 2021,
    DimDate[Month] = 4
)

[Anonymous]

@Fowmy 

I don't need to hard code values. I need for current month and last year same month.

Also I have start date and end date of employee. I need my calculation like the count of employees who were active in the company for current month and last year same month.

DimDate should be no where involved in my calculations.

[Fowmy]

@Anonymous 

In this case, please share a sample dataset that represents your actual data to understand the format.