Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 mo...
  • Anonymous's avatar
    Anonymous
    7 years ago
    -- 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 )
	)
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      I've got the solution... bear with me, please.

       

      Best

      Darek

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago
        -- 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 )
	)