Structure Excel and Model improve handling of multiple Slicers

[Anonymous]

Hi,

"Power Query" might be the ideal subforum for this question.

I do have a Data source from Excel with 2 tabs for some example companies. One tab Answers, another tab with CompanyInfo

In PowerBI I structured it like this:

1: Company Info. To connect the other tables via singular Name & ID. At the moment only 5 example-companies exist, but 20 companies will follow later...

 -> my factTable

2: Company Filters. (Nearly) all Filter-values for the companies. Some Toggle-Filters (F1,F2), some single-options (F3,F4), some multi-options (F5, F6)

-> a dimTable to slice by

3: Answers (Values) AND two Filters which "belong" to these values: Segment, SubSegment

 -> in between fact and dimTable. Because the values are "connected" to a Segment/ SubSegment I cannot (or at least: should not) distribute them to other tables

Model: To show proper values from "FactsAndFilters" filtered by the slicers in "CompanyFiltersSingle" I have to make the connection between my 3 tables bidirectional. But no conflict/circumnavigation takes place.

But: performance sucks. Is this due to this (bi-directional), or due to the number of slicers?

Could someone please show me a "better way" to structure A) the Excel and B) the Model?

https://www.dropbox.com/s/4stlfmky53m8z9x/Example8.pbix?dl=0

https://www.dropbox.com/scl/fi/3t4vhputoqcl2e7b4o8pz/Example8.xlsx?dl=0&rlkey=hiynv57oht647y0wnc4bys...

[amitchandak]

@Anonymous , I opened a file on my system, tried few filters, and it was working fine. May be because of limited data.

The first thing to check.

1) remove bi-directional and check; does it improve any performance?

[Anonymous]

Hi amitchandak,

on the limited data-set performance is ok.

On a larger dataset: I did remove some bi-directional connections - and speed improved drastically.

Some months ago I still created "monsters" like this one:

...with corresponding Performance-Impact:

I've learned a lot since then - but I still struggle with how to best structure multiple-options-slicers in A) DataSource (Excel) and B) Model in PowerBI.

Should I divide "slicers with single options" (On or Off, A or B or C) and "slicers with multiple options" (A, sometimes B, sometimes C...) into different dim tables?

Bye

Michael