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.

[Jude1122]

The explanation about set analysis overriding current selections makes sense. Using the * modifier looks like the simplest approach when you still want selections to affect the result, especially when filtering between categories such as High Net Worth and Institutional accounts. The flag approach is also a useful alternative if the same logic is used across multiple charts or the dataset is large, since moving the classification into the load script can keep the expressions cleaner and easier to maintain.