VLOOKUP and COUNTIFS in PowerPivot

# VLOOKUP and COUNTIFS in PowerPivot

• 2 august 2012 21:30

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

### Toate mesajele

• 3 august 2012 14:34

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/

• 3 august 2012 17:13

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.

• 6 august 2012 09:04

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