Forum Discussion

mmickle23's avatar
mmickle23
Regular Visitor
4 years ago
Solved

Report Filtering / Aggregation Issue

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.

 

 

Need Help
Tips and Tricks

3 Replies

  • ronaldbalza2023's avatar
    ronaldbalza2023
    Continued Contributor
    4 years ago

    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 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Community Support
    4 years ago

    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.