Forum Discussion

Anonymous

Not applicable

7 years ago

Solved

couting row using filter

Hello,

I am counting the number of row in a table1 which correspond to a criteria in table2.

For example,

Number of opportunities = DISTINCTCOUNT('Table2'[OpportunitySkey])

No. of Opportunities approved = calculate(counta('Table1'[ApprovedByRiskCommittee]),filter('table1','table2'[ApprovedByRiskCommittee]="Y"))

when I want to calculate the number of opportunities not approved, I am using the following equation but it does not work.

No. of Opportunities not approved = calculate(counta('Table1'[ApprovedByRiskCommittee]),filter('table1','table2'[ApprovedByRiskCommittee]<>"Y"))

The possible value for the approvedbyriskcommitte could be Y, N or blank

Does someone know what wrong with my dax equation

Regards,

parry2k
Super User
7 years ago
Anonymous do you have two tables? Why in the filter you have Table1
Anonymous
Not applicable
7 years ago
I am sorry. I have a mistake in my equation that I have provided.

Here's the good ones.
No. of Opportunities approved=
calculate(counta('Table'[ApprovedByRiskCommittee]),filter('table1','table1'[ApprovedByRiskCommittee]="Y"))

No. of Opportunities not approved=

calculate(counta('Table'[ApprovedByRiskCommittee]),filter('table1','table1'[ApprovedByRiskCommittee]<>"Y"))

The No. of opportunities approved is good.
The No. of opportunities not approved is not good maybe because the possible values for the
field ApprovedByRiskCommittee are Y, blank.
- evandrocunico
  Resolver III
  7 years ago
  Hi Anonymous try:
  
  calculate(counta('Table'[ApprovedByRiskCommittee]),filter('table1','table1'[ApprovedByRiskCommittee]<>"Y" || 'table1'[ApprovedByRiskCommittee] = blank()))
  
  regards.