Forum Discussion
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?
Anonymous
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
- parry2k
Super User
Anonymous I didn't worked much in excel , if you can share sample data and expected output that will help to get you the solution.
Anonymous
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.
