topic How to structure DataSource (Excel) vs PowerBI (Model) in DAX Commands and Tips

How to structure DataSource (Excel) vs PowerBI (Model)

Anonymous — Mon, 27 Sep 2021 13:25:04 GMT

Hi,

still struggle with how to best structure slicers - especially multi-option-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 AND sometimes B AND sometimes C...) into different dim tables? If so - how to do it?

Could someone please show me a "better way" to structure A) my Data-Source and B) the Model?

Here`s my example-source as well as the pbix:

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

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

Bye

Michael

Re: How to structure DataSource (Excel) vs PowerBI (Model)

VahidDM — Tue, 28 Sep 2021 00:10:02 GMT

Hi Anonymous

Can you add more details about how you want to use a slicer in your report? and add a sample of your data and result or describe the result with more information?

sorry, your request is not that clear.

Appreciate your Kudos🙏!!

Re: How to structure DataSource (Excel) vs PowerBI (Model)

Anonymous — Tue, 28 Sep 2021 10:05:16 GMT

Hi Vahid,

thank you!

I do not know how to best structure my model so that:

I get a fact-table containing info on the companies (Segment, SubSegment, Values per Segment/SubSegment, Name of company...)

I get my dim table to slice by different slicers:
- Toggle-slicers which only have TRUE or FALSE - F1 and F2 pictured below...
- Single-slicers which have ONE value - F3: either Glorb, Rok, Gnu or no Value. F4: either Gwerz, Glark or no value
- Multiple-options-slicers which can have several values - F5: is_regional AND sometimes is_listed AND sometimes is_bamo and F6: ...

I struggle with how-to-structure-my-filter-table (the picture is of the table after I pivoted several "multiple-slicer-options" columns).

At the moment this is my data model:

Because OrdnerTabelleFilters contains Filters as well as Filters' values for my companies I have to make the connection bi-directional.

My questions are:

Should I create one table containing values for ALL types of slicers (toggle, single, multi-option)?

Should I create a table containing values for EACH type of slicers?

If structured differently (separate Filters and Filter's values) - how do I connect the dim/fact tables?

Here`s my example-xlsx-source as well as the pbix:

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

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

Bye

Michael