Chart Selection not working because of Set Analysis

[JFDemers]

Hi,

I have an app with KPIs and pie charts that are powered by master items. One of the KPI has the below Measure to count the number of accounts in scope of that app.

=COUNT({<AccountClientType={"Institutional","High Net Worth"},AccountCode -= {"40*","48*","49*","5*","6*","9*"}>} DISTINCT AccountCode) +
COUNT({<AccountCode = {"99988"}>} DISTINCT AccountCode)

My problem is that whenever I want to do a selection on 'High Net Worth' on a chart for example and only see that category, nothing happens. All other selection on other dimensions work has intended.

Thx

[rubenmarin · Accepted solution]

Hi, that's because the set analysis is overwriting your selections, if you want to calculate based on set analysis and selections you can add an * to the values assigned in set analysis:

=COUNT({<AccountClientType*={"Institutional","High Net Worth"},AccountCode -= {"40*","48*","49*","5*","6*","9*"}>} DISTINCT AccountCode) +
COUNT({<AccountCode = {"99988"}>} DISTINCT AccountCode)

[barnabyd]

G'day @JFDemers,

Another option is to calculate a flag in the load script like this:

load <other fields>,
    if( match( AccountClientType, 'Institutional', 'High Net Worth' ) > 0
      , 1, 0 ) as AccountFlag
from <table>;

This will simplify your set analysis and won't affect selections by the user on the AccountClientType field.

=COUNT({<AccountFlag={1},AccountCode -= {"40*","48*","49*","5*","6*","9*"}>} DISTINCT AccountCode) + COUNT({<AccountCode = {"99988"}>} DISTINCT AccountCode)

Actually, I would take this idea a step further and move the list of account code numbers into a flag as well. This improves performance for the user if the data set is large.

I hope this helps.

Cheers, Barnaby.

[JFDemers]

Works like a charm!

Thx a bunch!

[JFDemers]

Thx a lot for the help! I will give it a shot.