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

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

    First of all, Thank you so much!

     

    Second, sorry about the long time without accepting it as a solution, things are crazy here.

     

    And last, Do you know what should I do if i wanted to see the analist average for more then one at once?

     

    For example, I have 15 analists here, I want to know the average of their averages.

     

    Thank you again.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Of course I do :)

       

      [Analyst Average 2] =
var __result =
	AVERAGEX(
		VALUES( FactTable[Analyst Name] ),
		[Analyst Average]
	)
return
	__result

      If only one analyst is visible in the current context, this measure will return exactly the same value as [Analyst Average] (the prior measure). So, you could treat this one as an extension of the previous one and hide the previous one (also rename both). The rule is that if a measure starts with _, then it should be treated as an internal model measure that should not be exposed to the end user. So, you'd name [Analyst Average] to something like [_AnalystAverage] and the latest one to [Analyst Average].

       

      Best

      Darek

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        I love you man, You have no ideia how much you've helped me hahah

         

        Wish you all the best.