cancel
Showing results for
Did you mean:
Regular Visitor

## Adding COUNTIF  as a custom measure

I have 2 pivot tables  with following details

 Name Allocation

I am using COUNTIF to identify if Name from Table 1 is present in Table 2 and if yes, what is the allocation.

=IF(COUNTIF(\$I:\$I, \$A6)=0, "Not Planned in FY21", IF(SUMIF(\$I:\$I, \$A6,\$J:\$J) =1, "100% planned in FY21",IF(SUMIF(\$I:\$I, \$A6,\$J:\$J) =0, "Needs Project Allocation","Improve Allocation")))

A6 is name in table 1

I is the name column of Table 2

J is the alloation column of Table 2

Can I add above function as a custom measure?

1 ACCEPTED SOLUTION
Community Support

There are some differences in DAX compared with Excel.

For your requirements, it can be something like this:

Measure = CALCULATE(MAX('Table1'[Allocation]),FILTER('Table','Table'[Name] in VALUES('Table2'[Name])))

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support

There are some differences in DAX compared with Excel.

For your requirements, it can be something like this:

Measure = CALCULATE(MAX('Table1'[Allocation]),FILTER('Table','Table'[Name] in VALUES('Table2'[Name])))

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User III

@khenatram I didn't worked much in excel , if you can share sample data and expected output that will help to get you the solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Announcements

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.