Set Analysis isn't adjusting based on selection

[mcowley]

Hi All,

I've got a table where there are 3,920 distinct values as a key field (op_HAR).  I've got a dimension op_BNP_Compliance_Flag with a YES/NO flag and 100% density.  Of the total 3,920  rows, there are 3,528 with Yes and 392 with No.

I wanted to create a compliant KPI such as:

=Count({$<op_BNP_Compliance_Flag={'Yes'}>} Distinct [op_HAR]) /  count(DISTINCT op_HAR)

which would resolve to 3,528 / 3,920 - or 90%.

However, when I select "No" in the compliance flag dimension, the first set analysis part of my KPI is not affected by limiting the rows to "No", and the KPI resolves to 3,528 (Yes rows) / 392 (Total rows affected by the No selection)- or 900%.

By using the $ set modifier, shouldn't the numerator in the KPI resolve to 0 when 'No' is selected?

[GaryGiles · Accepted solution]

@mcowley 

When you explicitly use a field in set analysis, it will override any selections that are made in that field.  So the Numerator will always have op_BNP_Compliance_Flag={'Yes'} regardless of what you select in the field.  If you want a selection of No to cause the numerator to go to 0, you should be able to use:

Count({$<op_BNP_Compliance_Flag={'Yes'}>*<op_BNP_Compliance_Flag=$::op_BNP_Compliance_Flag>} Distinct [op_HAR]) 

[Vegar · Accepted solution]

As @GaryGiles is saying, the behaviour of your expression is as expected.

You can get your desired output by adjusting the modifier like this. 

=Count({$<op_BNP_Compliance_Flag*={'Yes'}>} Distinct [op_HAR]) / count(DISTINCT op_HAR)