Search not working in Analysis

[Coolavin]

The search feature isn't working. 

When I click the magnifying glass button on the status column and then select Active.

It drops the Active totals down to 818,774 it should be 1,024,959.

It works correctly when I select the Inactive. Hopefully someone can help me figure this out. 

[lorenzoconforti]

What is the formula behind the Active Credential KPI? 

If you create a filter pane on Status and select Active from there do you see the same behaviour?

[Coolavin]

The formula for the Active Credential is:

=Sum(aggr(COUNT( {$-<[CredentialExpireDate]={'*'}> + <[CredentialExpireDate] = {'>=$(=Date(Today()))'}>}[CredentialMeasure]),[CredentialMeasure]))

If I create a filter pane and use the same expression that is in Status it has the same behavior.  

[lorenzoconforti]

I'm guessing the Status column in your table follows the same logic of the set expression you have just shared

It's fairly complex as you are filtering based on the result of a set expression; to help you further I would need the application 

I would implement this differently and calculate the filed in the loading script, assuming of course you are reloading the data every day

[Coolavin]

The expression in Status is as follows:

=If([CredentialExpireDate] < Today(), 'Inactive', If([CredentialExpireDate] >= Today(), 'Active', If(aggr(alt(count(CredentialExpireDate),0),primarykeyfieldofothertable)=0,'Inactive', 'Active')))

It tries to follow the same logic as the one in the Active Credential KPI though the Status is a dimension and the Active Credential KPI is a measure.

We don't upload the data daily. It's an adhoc upload when we get a new dataset, it's at random times. 

[lorenzoconforti]

Looks like the two formulas work in different ways

In the KPI one, you remove any entry that has got a CredentialExpireDate (which means you consider as Active the ones that are null)

In the table you have an if clause where if count(CredentialExpireDate) evaluates to a value (i.e. if CredentialExpireDate exists) your if test returns False and you tag it as Active (i.e. you tag as active the fields where CredentialExpireDate is not null); this looks to me different from what you do in your KPI object

Try using a measure in your table rather than a dimension so that you can use the same logic as the KPI; you should at least get the same result:

=if( Sum(aggr(COUNT( {$-<[CredentialExpireDate]={'*'}> + <[CredentialExpireDate] = {'>=$(=Date(Today()))'}>}[CredentialMeasure]),[CredentialMeasure])) > 0, 'Active', 'Inactive')

I would do it at a script level and refresh the dashboard daily (so that you pick today's date correctly) even if you don't have new data to load.

Also, the totals in the 3 KPI's don't match; there is a small difference (probably you are aware of this but worth pointing out before there is something else that is not working)

[Coolavin]

How would you recommend getting the two different formulas to work the same way?  Oh, right with using a measure in the table instead of a dimension. I tried that but it kept giving me errors. I will try again. 

[lorenzoconforti]

Try this in your dimension:

=If([CredentialExpireDate] < Today(), 'Inactive', If([CredentialExpireDate] >= Today(), 'Active', If(aggr(alt(count(CredentialExpireDate),0),primarykeyfieldofothertable)=0,'Active', 'Inactive')))

Can you please explain what makes an account active?

According to the Active KPI:

- you take all the records ($)

- you remove the non null CredentialExpireDate (-<[CredentialExpireDate]={'*'}> )

- you add the ones where CredentialExpireDate is larger or equal to today (<[CredentialExpireDate] = {'>=$(=Date(Today()))'}>) it seems like you are double counting these as you have taken them already in the first step; this might be your issue; you might just need to get rid of the first element

[Coolavin]

Ok, when I try to add a measure in the table and use the same formula it gives me and out of calculation memory error. Is there a way you can recommend to resolve that error?

[lorenzoconforti]

Sorry, in your dimension, not measure; update what you currently have. In theory this change should make it match with what you have in the KPI object:

=If([CredentialExpireDate] < Today(), 'Inactive', If([CredentialExpireDate] >= Today(), 'Active', If(aggr(alt(count(CredentialExpireDate),0),primarykeyfieldofothertable)=0,'Active', 'Inactive')))