Report Filtering / Aggregation Issue

[mmickle23]

Hello-

I was hoping I could get some help.  I am trying to do a project regarding MLB in PowerBI.  When I create a simple report it does not aggregate the numbers correctly.  I was hoping that someone could tell me what I was doing wrong?

It aggregates on the player only.  However, I need it to aggregate by the player and the year at the same time.  Here is the example of the data and the report:

Any help would be greatly appreciated.

[v-yadongf-msft]

Hi @mmickle23 ,

From your description, there is no active relationship between your two tables, and the data of the two tables cannot be directly filtered,  but indirectly filtered through the third table. If you create the wrong model relationship, it may result in incorrect aggregation.

When evaluating ambiguity, Power BI chooses the filter propagation path according to the following precedence rules. The first rule match determines the path it will follow.

• A path consisting of one-to-many or one-to-one relationships from filter to target. The filter must flow from the "one" side of a relationship to the other side for all relationships along the path.
• A path consisting of one-to-many relationships from filter to an intermediate table followed by many-to-one relationships from intermediate table to target. The filter must flow from the "one" side of a relationship to the "many" side for all one-to-many relationships, and from the "many" side of a relationship to the "one" side of the relationship for many-to-one relationships in the path.
• Any other path, including paths that contain many-to-many relationships.

For more information, please refer to:

Create and manage relationships in Power BI Desktop - Power BI | Microsoft Docs

Model relationships in Power BI Desktop - Power BI | Microsoft Docs

Solution: Please select the correct activate relationship between the two tables and try again.

If you still have questions, please provide us with sample .pbix file after hiding sensitive information.

Best regards,

Yadong Fang

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

[Anonymous]

If this is one of your first times using powerBI welcome to the community and enjoy the learning.

It appears you are using a Table visualisation to display your data. Table visuals work (essentially) like the Data view for the report creator. It shows the data in a tabulated format and allows you to filter by columns.

For aggregation purposes (grouping data under 1 or more types of filters), you will want to use the Matrix visualisation. This will let you group in rows and columns, and can even have multiple columns that you can drill down into which lets you explore data further.

RE your specific question - Use a Matrix visualisation. Put [fullname] in the rows field, and put [yearid] in the Columns field.

This should group player information by the relevant year.

[ronaldbalza2023 · Accepted solution]

Hi @mmickle23 , hope you are having a great day. You have to work on your model first before anything else. I see many to many relationship between your fact and dim table which is unusual not unless you really understood how it is being used. Otherwise, you can create a dax using userelationship function. DAX Filter - USERELATIONSHIP function 

Understanding relationships in Power BI Desktop 
Power BI Relationship: Many to Many & Bi-Directional Filtering #Q&A 
Data Modeling (Star Schema 🌟) in Power BI – Creating Dimension Tables