CountIf and match

[Evan0211]

I am trying to build a KPI that is off of an if statement but I am getting incorrect counts.

In my table, I am returning one data item, but my KPI is returning a count of 64.

Here is the code I am using:

=Count(if(Match([OFFICE_CODE], $(vFilterCodes))
and [Stage] <> 'Pre'
and
(not isnull(E_Completed_Date)
and
not isnull(AD_Completed_Date))
, [ID]))

In the table, I am using this as a diminsion: 

if(Match([OFFICE_CODE], $(vFilterCodes))
and [Stage] <> 'Pre'
and
(not isnull(E_Completed_Date)
and
not isnull(AD_Completed_Date))
, [ID])

The table returns 1 ID, which is correct. The KPI is showing 64.

[Daniel_Pilla]

Hi @Evan0211 ,

I would highly suggest using set analysis instead of if() statements. For example, 

=Count({<OFFICE_CODE={$(vFilterCodes)},Stage-={'Pre'},E_Completed_Date={"*"},AD_Completed_Date={"*"}>}ID)

[Evan0211]

Thank you Daniel, however I am still getting 64 for the KPI when the table only returns 1.

[BrunPierre]

Perhaps, with set analysis.

=Count({$< [OFFICE_CODE] = {"$(=vFilterCodes)"}, [Stage] -= {'Pre'}, E_Completed_Date = {'*'}, AD_Completed_Date = {'*'} >} [ID])

[Daniel_Pilla · Accepted solution]

Hi @Evan0211 ,

I am attaching a sample application that shows you how to do it and also illustrates how to do it using set analysis. Here is the set analysis (you may have to tweak depending on how you setup your variable):

=Count({<OFFICE_CODE={$(vFilterCodes)},Stage-={'Pre'},E_Completed_Date={"*"},AD_Completed_Date={"*"}>}ID)

 Please find the app attached.

[Evan0211]

Thanks. For some reason, I am still getting 64 in the KPI and only 1 in the table. 

[Daniel_Pilla]

Hi @Evan0211 ,

What is the expression for the dimension in the table? If you only want a single dimensional column with the IDs that match your criteria, you could use:

=Aggr(
	Only({<OFFICE_CODE={$(vFilterCodes)},Stage-={'Pre'},E_Completed_Date={"*"},AD_Completed_Date={"*"}>}ID),
    ID
)

Otherwise, the app should show you how everything works.

Cheers,

[Evan0211]

Daniel, I had an erroneous typo and now it is showing 1 using your method. Thank you so much!