Slicers showing more data than fact table.

[Copycat]

Good day beautiful people,

I have 3 dimension tables and 5 fact tables.

Dimension tables are:

Calendar, column Date

Product, columns matID, location

Location, columns matID, location

(There are of course more columns)

Fact tables:

All of them got multiple different columns but all contain matID, date and location.

Fact - dim, many to one by date, matID and location. So each fact table got 3 relationships.

For my slicers, I'm using dim columns.

For example:

Table Product: matID, location, description, info1, info2 and so on.

On a slicer I want to show info2, then another with info1 and on another matID just for the table I'm using data from.

I can do it with simple measure with columns that exist in both: fact and dim, but I cannot do that with columns like info1, info2.

Also, I have a problem with tables where I'm using data from different tables; mix of fact table1, column from fact table2 and so on.

Generally if someone uses a slicer, other works okay, as intended, but there is a chance that they will select non existing record which is no-go.

Appreciate all help!

Tldr; Slicers based on dim tables, should show data only available in fact tables.

[v-zhengdxu-msft · Accepted solution]

Hi @Copycat 

You can create calculated columns or measures in your fact tables that reference the dimension table columns. This way, you can use these calculated columns in your slicers. For example, create a calculated column in your fact table that references 'info1' from the 'Product' dimension table.

To avoid selecting non-existing records, you can use DAX functions like 'RELATED' or 'LOOKUPVALUE' to ensure that slicers only show data that exists in the fact tables.
You can also use FILTER and CROSSJOIN functions to create a table that only includes valid combinations of matID, date, and location.

For example, you can create a new table:

ValidCombinations =
FILTER (
    CROSSJOIN ( FactTable1, FactTable2 ),
    FactTable1[matID] = FactTable2[matID]
        && FactTable1[date] = FactTable2[date]
        && FactTable1[location] = FactTable2[location]
)

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bhanu_gautam]

@Copycat Ensure that your relationships between fact and dimension tables are correctly set up. You can create measures that filter the slicer values based on the data available in the fact tables.

Create calculated columns or tables that only include the values present in the fact tables. This can be done using DAX functions like INTERSECT or FILTER to ensure that only relevant values are shown in the slicers.

you can sync slicers across different pages to ensure consistency.

Implement dynamic filtering using DAX to ensure that slicers only show values that exist in the fact tables. For example, you can create a dynamic table that only includes values present in the fact tables and use this table for your slicers

FilteredProductTable =
FILTER(
Product,
Product[matID] IN VALUES(FactTable1[matID]) &&
Product[location] IN VALUES(FactTable1[location])
)