Filtered table different value when put in slicer

[Applicable88]

Hello,

i have a fact table and a material table, which unfortunately are connected via a m:n relationship:

Factable:

Order	Date	Type	Status	Category	Material
12345	01.01.2021	5LL	DLVR	Manu	ZZZ
12346	02.01.2021	8ZL	DLVR	Manu	ZZZ
12347	03.01.2021	888	PRCC	Manu	ZZZ
12348	04.01.2021	888	PRCC	Plan	ZZZ
12349	05.01.2021	888	PRCC	Plan	ZZZ
12350	06.01.2021	888	PRCC	Manu	BB
12351	07.01.2021	888	PRCC	Manu	BB

I grouped another table which is the materialtable with summarize() and used addcolumn to the sum of Reserve for a material. I grouped to date and material and amount where I got the above table.

Summarized:

Material	TotalAmount	Date	Reserve
ZZZ	2000	06.07.2021	11
ZZZ	2000	06.07.2021	58
ZZZ	2000	06.07.2021	33
ZZZ	2000	06.07.2021	77
LL	3000	07.07.2021	55
LL	3000	08.07.2021	44
BB	2000	09.07.2021	33
BB	2000	10.07.2021	55

I connected the summarized table with the original material table. I can of course connect it with the material column of the facttable as well. They are all connected with m:n anyway.  So far I still would get the same return. I connect them so I can use the standard filter pane of PowerBi to filter only those material I want to look at, like Category must be "Manu" , type must be....

The whole purpose was to get the cummulitve sum of Reserve which will be substracted of the total amount:

RangeSum = CALCULATE(sum(Summarized[TotalAmount]))-calculate(sum(Summarized[SumOfReserve]), filter(allselected(Summarized), Summarized[Date].[Date] <= max(Summarized[Date])))

The confusing thing here is now: When I just drag material, the date and the rangesum measure into a matrix I get really strange numbers. The bold part of the RangeSum function is so big as if nothing was filtered out and I always get minus values.

But strangely setting up a slicer where I can chose directly summarized[material] the numbers got correctly filtered out and also changed. But its actually from the same summarized table. I really dont know why. Why the slicer put it in the right filter context, but by default the matrix showing weird numbers?

To double Check I made a dummy Table where I manually put in all the materials of interest and ignore the others. I put this table in between the summarized one and facttable. This time its a 1:n relationship. And it filters out already correctly in the default mode.

What do I have to do to get the summarized table right? 

Thank you very much in advance.

Best.

[BA_Pete · Accepted solution]

Hi @Applicable88 ,

At first glance, this looks like a standard modelling issue. You should be aiming to create a star-schema in your data models, and absolutely SHOULD NOT be using MANY:MANY relationships.

Your dimensions (non-numeric identifiers) should be in dimension (dim) tables. Your dim tables, at the very least, should contain a UNIQUE list of your dimensions.

For example, a dimMaterial dimension table would look like this:

Material

ZZZ

LL

BB

A dimCategory table would look like this:

Category

MANU

PLAN

There's a number of quick and simple ways you can dynamically create these tables from your existing data.

You then relate dimMaterial[Material] to all of your factTable[Material] fields (ONE:MANY), and make sure you use dimMaterial[Material] in any slicers and filters.

For dimensions such as [Category] that only exists in one fact table, you will need to find a way to add this field to the fact table that doesn't have it if you want this table to also be filtered when you apply slicers.

This can be done in Power Query either using Merge, or programmatically populating the correct value in a new column based on other identifiers that are available.

Pete

[v-yingjl · Accepted solution]

Hi @Applicable88 ,

Many-to-many relationship is a week relationship that you have better to prevent it. 

Usually, we create a 'Dim table' or 'Bridge Table' to transfer it to many-to-one relationship.

Relate many-to-many dimensions guidance

When you have a many-to-many relationship between dimension-type tables, we provide the following guidance:

• Add each many-to-many related entity as a model table, ensuring it has a unique identifier (ID) column
• Add a bridging table to store associated entities
• Create one-to-many relationships between the three tables
• Configure one bi-directional relationship to allow filter propagation to continue to the fact-type tables
• When it isn't appropriate to have missing ID values, set the Is Nullable property of ID columns to FALSE—data refresh will then fail if missing values are sourced
• Hide the bridging table (unless it contains additional columns or measures required for reporting)
• Hide any ID columns that aren't suitable for reporting (for example, when IDs are surrogate keys)
• If it makes sense to leave an ID column visible, ensure that it's on the "one" slide of the relationship—always hide the "many" side column. It results in the best filter performance.
• To avoid confusion or misinterpretation, communicate explanations to your report users—you can add descriptions with text boxes or visual header tooltips

We don't recommend you relate many-to-many dimension-type tables directly. This design approach requires configuring a relationship with a many-to-many cardinality. Conceptually it can be achieved, yet it implies that the related columns will contain duplicate values. It's a well-accepted design practice, however, that dimension-type tables have an ID column. Dimension-type tables should always use the ID column as the "one" side of a relationship.

Please refer:

1. Many-to-many relationship guidance 
2. Many-to-One or Many-to-Many? The Cardinality of Power BI Relationship Demystified 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BA_Pete · Accepted solution]

Hi @Applicable88 ,

At first glance, this looks like a standard modelling issue. You should be aiming to create a star-schema in your data models, and absolutely SHOULD NOT be using MANY:MANY relationships.

Your dimensions (non-numeric identifiers) should be in dimension (dim) tables. Your dim tables, at the very least, should contain a UNIQUE list of your dimensions.

For example, a dimMaterial dimension table would look like this:

Material

ZZZ

LL

BB

A dimCategory table would look like this:

Category

MANU

PLAN

There's a number of quick and simple ways you can dynamically create these tables from your existing data.

You then relate dimMaterial[Material] to all of your factTable[Material] fields (ONE:MANY), and make sure you use dimMaterial[Material] in any slicers and filters.

For dimensions such as [Category] that only exists in one fact table, you will need to find a way to add this field to the fact table that doesn't have it if you want this table to also be filtered when you apply slicers.

This can be done in Power Query either using Merge, or programmatically populating the correct value in a new column based on other identifiers that are available.

Pete