Field Level Filtering

Question & Answer
, ,
1

We have a single sheet with the fields

  • DRUGS
  • COUNT_OF_MEMBERS
  • TOTAL_COST
  • PRESCRIPTION_COUNT

where,
COUNT_OF_MEMBERS = distinct_count({MEMBER_ID})
Total Cost = sum(paid_amount)

We apply a provider filter for a provider named ‘ABC’ and we expect to only see a COUNT_OF_MEMBERS for a provider ‘ABC’ but the TOTAL_COST and PRESCRIPTION_COUNT needs to be displayed for all the providers, which means the amount needs to be summed up for those member irrespective of the provider filter. Is there any solution for this?

Note : There are a total of 25 filters in the dashboard, all filters except provider should be applied consistently in the sheet.

2

@darcoli Do you know any solution to this?

3

@smriti71 If you can live with a single-select filter, then for the Provider one specifically we can drive that using a parameter inside your calculation (and not actually create a filter for it). Make a parameter for Provider, add a control for it (can tie the values to the Provider field) and then in your calculation do it like this:

distrinct_count(ifelse(Provider = ${ProviderParam}, {MEMBER_ID}, null))

This field will be ‘filtered’ by the parameter, but none of your other fields will. All fields will respond to all other filters.

4

@Jesse This works when I select a single PROVIDER in parameter control.

In the initial screen, I would want to put provider control default as ‘All’ instead of any single provider. When trying to achieve this, it shows NO DATA visual as it could not match the value in the provider field.

Is there any way this can be addressed?

5

@Jesse This gives the TOTAL_COST of all the members for a drug.

For Example:

For Provider ‘ABC’ selected,
Member Count = 5

I need a Total Cost of those 5 Members for ALL THE PROVIDERS - meaning provider control is just used to narrow down the members. And using those members, we have to find a TOTAL_COST for those 5 members. Can it be achieved?

Right now, it results in the TOTAL_COST of all the members for all providers and not those 5 members.

6

You can add another value to your parameter control with whatever text you want like ‘ALL’, and then add one more piece of logic to your calc to allow for an All choice.

distinct_count(ifelse(Provider = ${ProviderParam} OR ${ProviderParam}=‘ALL’, {MEMBER_ID}, null))

8

@Jesse Do you have a solution to this?

9

Im not sure I understand. Can a member have multiple providers?

10

Yes, they can have multiple providers.

11

Apologies I was traveling last week. This is a tricky one. There may be a solution using Level Aware Calculations (LAC) to pre-calculate the total cost across all providers for each member, then even is we filter to one provider, the total cost for that member across all providers will still be there. However its hard to say if this will work without trying it one your data. Any chance you can share a sample data set we can experiment with?