Hi Anna,

you can use PU functions for this. They allow to calculate an aggregation based on a grouper while not respecting FILTERs.

The formula depends on how your tables in the data model look like, but it should be something like this:

Best

David

Hi David,

thanks for your quick response. I have amended the suggestion a bit, as i would like to pull the distinct number of cases into my calculation. However, with the below formula, i do not think it is taking into account to only include the case count where Rejection Flag=1., as the results bring in the total number of distinct cases.

COUNT(DISTINCT CASE WHEN (PU_COUNT_DISTINCT(DOMAIN_TABLE(CASES.LocalMarket, CASES.RejectionFlag), CASES.RejectionFlag) =1.0)

THEN CASES."_CASE_KEY" ELSE NULL END)

Please advise.

Thanks

Anna

Hi Anna,

here you are checking if the distinct rejection flag count equals 1, instead of counting how often the rejection flag equals 1. Since youve included the RejectionFlag also in the DOMAIN_TABLE, you are now counting the number of distinct RejectionFlags per LocalMarket and RejectionFlag, which is always 1. This is why the CASE WHEN condition is always true.

Can you please specify in more detail what the exact problem is which you are trying to solve? So which tables and columns are relevant, and how should the KPI be calculated exactly?

Best

David

Hi David,

I have an OLAP table that as a dimension has CASES.LocalMarket And for KPI is: Count(DISTINCT CASE WHEN CASES.RejectionFlag = 1.0 THEN CASES."_CASE_KEY" ELSE NULL END)

There are also several filter options in this dashboard. for example. a drop down for rejections reasons. When someone selects an item from this drop, i do not want those numbers in my KPI to change. (hence why i tried using the static variable)

I hope this is now more clear.

Thanks,

Anna

Hi Anna,

can you try to use the following KPI instead of the current one:

This should be the same query, but only as a PU function, which does not respect the filters. Note that if a LocalMarket value is filtered out completely, you also will not see that as a row in the OLAP table. For every LocalMarket value that survives your filter, however, you should see the total number of cases with a RejectionFlag=1 for that LocalMarket.

Hope this works.

Best

David