Forum Discussion
Change Column Values When Filter Is Applied
Hi,
My main goal is to calculate the average of cases closed the employees have per day, but I'm only counting the days they close more than 2 cases.
So, If the date of closure is repeated more than two times on the table, theres a column representing it as 1.
1 for working days and 0 for non working days
The problem is:
When I filter a analist, the working day column doesnt filter.
So, If a day is marked as working day in the big picture, it will always be a working day even if the analist hasnt closed more than 2 cases.
Is there a way to change it?
-- measure you need first [# Closed Cases] = DISTINCTCOUNT( FactTable[Case ID] ) -- 'Calendar' must join to the FactTable on a Date field -- (the other field is [Date Of Closure] -- and be designated as Date Table in the model. Please make -- sure you follow the rules of creating a proper Calendar. -- Without a good calendar a model is almost worthless and -- prone to errors. [Analyst Average] := var __onlyOneAnalystVisible = HASONEFILTER( FactTable[Analyst Name] ) var __minNumOfCases = 2 var __numOfWorkedDays = COUNTROWS( FILTER( 'Calendar'[Date], [# Closed Cases] >= __minNumOfCases ) ) var _totalNumOfCases = [# Closed Cases] RETURN if( __onlyOneAnalystVisible, DIVIDE( __totalNumOfCases, __numOforkedDays ) )
11 Replies
Not enough info on the model and how it works currently. An example with some pics would be helpful. Please, try to understand that you've been working with the model for some time now and you do understand it. Others don't and thus need to be given much more info than you'd need to be able to understand.
Thanks.
Best
Darek
I think I´ve made some progress on my own, basically, all I need now is to have a correct total on the "Worked" Column.
How It Works:
The "Worked" measure is set to be "1" everytime the "Cases" column is bigger than 2.
What that says: Everyday that there were more than 2 cases, is considered a worked day.
I want to Sum the ones on the "Worked" Column to be able to divide the total cases by the total of worked days