Filter to only show Relevant Dimension Values

Morning Sisense community!

I have my Fact Table and a related Dimension Table. My dimension table contains more unique values than are actually present in the fact table.

When I use a field from the Dimension Table as a filter, the filter displays all values from the dimension, even those that have no corresponding fact records. I only want the filter to show dimension values that are relevant to the data in the fact table.

Example Tables:

fact_table		dimension_table
id	dim_id	dim_id	dim_name
1	1	1	one
2	2	2	two
3	1	3	three
4	2	4	four

fact_table and dimension_table have a relationship in the EC based on dim_id

Desired Outcome: In the filter based on dim_name, I only want to see "one" and "two", but currently, I see all four: "one," "two," "three," and "four."

How do I achieve my desired outcome?

Luke Flett

Posted 10 months ago·Last reply 8 months ago

5 comments

DRay

·8 months ago

Hello Laflet,

I’m following up to see if the solution offered by worked harikm007 for you.

If so, please click the 'Accept as Solution' button on their post. That way others with the same questions can find the answer. If not, please let us know so that we can continue to help.

Thank you.

DRay

·9 months ago

Hello Laflet,

I’m following up to see if the solution offered by worked harikm007 for you.

If so, please click the 'Accept as Solution' button on their post. That way others with the same questions can find the answer. If not, please let us know so that we can continue to help.

Thank you.

DRay

·10 months ago

Hi Laflet,

I’m following up to see if the solution offered by worked harikm007 for you.

If so, please click the 'Accept as Solution' button on their post. That way others with the same questions can find the answer. If not, please let us know so that we can continue to help.

Thank you.

DRay

·10 months ago

Hi Laflet,

I’m following up to see if the solution offered by worked harikm007 for you.

If so, please click the 'Accept as Solution' button on their post. That way others with the same questions can find the answer. If not, please let us know so that we can continue to help.

Thank you.

Hari KM

·10 months ago · Edited

Hi Laflet ,

We can do it using 'Custom' option in the dashboard filter.
By default, when you use a field from a Dimension table, Sisense shows all values from that table, even those not linked to any fact records.
Add below jaql in the 'Custom' option of the filter and apply. Then set it as background filter. Here, replace [fact_table.Id] with the table name and field name of your fact table column.

{
  "attributes": [
    {
      "dim": "[fact_table.Id]",
      "filter": {
        "exclude": {
          "members": [
            "N\\A"
          ]
        }
      }
    }
  ]
}

Please let me know if you have any questions

-Hari