VLOOKUP and COUNTIFS in PowerPivot

Question

I have the following table in power pivot

Incident Activity Analyst

1 Create George

1 Resolve Bill

1 Close George

2 Create John

2 Close John

What I need to obtain is the folling:

• How many incidents did each Analyst Resolve, the problem is when they close directly they are actually resolving

In normal excel I would add a column saying Resolve where I would use the following formula:

=IF(B1="Resolve";1;IF(AND(COUNTIF(A:A;A1;B:B;"Resolve")=0;B1="Close");1;0))

In that column I will have a 1 for each resolve and a 1 for each close where the count of resolve for that incident is 0.

I can also resolve that using vlookup creating a new table where I would only have one row for each incident a column for resolve where I would look for the resolve analyst and if that look is an error look for the close analyst.

But I can do neither the VLOOKUP neither the COUNTIF in the powerpivot.

Any Help Would be appreciate

Answer 1

Hi Sebastian

You can generally match the behavior of VLOOKUP and COUNTIF in PowerPivot using DAX formulas.

One question: On Incident 1, Analyst "Bill" resolved it, but George closed it.  Should that count as a resolution to Bill or George?

---

Javier Guillen
http://javierguillen.wordpress.com/

Answer 2

Thanks Javier Incident 1 should count as a resolution for Bill. 

I try emulating the vlookup and the countif with DAX formulas but I'm not very good with them so any help would be appreciated.

Answer 3

Hi Sebastian,

Here's one way to resolve it.

Step 1 is adding a calcuated column ResolvedBy.

=IF(CALCULATE(COUNTROWS(Table1),ALLEXCEPT(Table1,Table1[Incident])) = 2
  && CALCULATE(VALUES(Table1[Analyst]),Table1[Activity]="Create", ALLEXCEPT(Table1,Table1[Incident])) =
  CALCULATE(VALUES(Table1[Analyst]),Table1[Activity]="Close", ALLEXCEPT(Table1,Table1[Incident]))

 ,CALCULATE(VALUES(Table1[Analyst]),Table1[Activity]="Close", ALLEXCEPT(Table1,Table1[Incident]))
 ,CALCULATE(VALUES(Table1[Analyst]),Table1[Activity]="Resolve", ALLEXCEPT(Table1,Table1[Incident]))
)

If there are only two rows for this particular incident, and the incident is created and closed by the same analyst look up the name of this analyst.

In all the other cases we lookup the analysis who resolved the inciditent.

Step 2 is creating a measure which counts the DISTINCT Resolved columns.

=DISTINCTCOUNT('Table1'[Incident])

And this is the result

Incident	Activity	Analyst
1	Create	George
1	Resolve	Bill
1	Close	George
2	Create	John
2	Close	John
3	Create	George
4	Create	John
4	Close	John
Row Labels	Distinct Count   of Incident
1
Bill	1
John	2
Grand   Total	4
Incident	ResolvedBy
1	Bill
2	John
3
4	John