Trouble filtering on denormalized model

[ps1]

I have a data table with >100 millions rows I have determined that joins to dimension tables are costly. I have moved the dimensions columns directly to the large data and I can rewrite the DAX queries such that they use those filters. The problem is I also have other data tables, indirectly related to the large data table, that also need to use those same filters for the same denormalized dimension columns. How do I get one slicer to filter several unrelated tables based on the same column names. So Table1[colA], Table2[colA], Table3[colA] can't be related but all need to be filtered from ONE slicer that is providing the value for colA?

Thanks

[Anonymous]

Hi @ps1 ,

According to your problems, here are my answers.

If your denormalized dimension columns are in dimension tables, you can create a measure to filter slicers. You can create a measure like this:

Filter Slicers = COUNTROWS(RELATEDTABLE('Table'))

 You can also look at this topic for some helps: Solved: Power BI One Slicer to filter other slicers - Microsoft Fabric Community

Also, you can create a separate table that contains all the unique values from your denormalized dimension columns.

Use the following formula to create the new table.

Names = DISTINCT (
    UNION (
        VALUES (Table1[Requested By]),
        VALUES (Table2[Requested By]),
        VALUES (Table3[Requested By])
    )
)

Now you can  create relationships from this new “Names” table to each of your other tables, use the "Requested By" column from "Names" tables as your slicer. Read this document may help you more: powerbi - One Slicer for Power BI Desktop affecting multiple tables - Stack Overflow

Best Regards

Yilong Zhou

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

[amitchandak]

@ps1 , Make sure the join is 1-m Single directional and they are numeric join. Then they should work well. Usually, a large dimension can slow down. But small dimensions with large facts should work

Now if you want use value between disconnected tables you can use filter or treatas

calculate([Measure], filter(Table1, Table2[Col1] in values(Table1[Col1]) ))

or

CALCULATE([Measure],
TREATAS (values(Table1[col1]) ,
Table2[Col1]  )
)

https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

https://docs.microsoft.com/en-us/dax/treatas-function