Forum Discussion

afaro's avatar
afaro
Icon for Helper III rankHelper III
2 years ago

Filtering many-many relationships not working

I have a Calendar table with the following years: 

YearQuarter
20221
20222
20223
20224
20231
20232
20233
20234
20241
20242
20243
20244

 

I have a Sales table with the following data: 

TransactionRegionYearRevenue
AW20231
BX20232
AY20234
BZ20233

 

My filter context in the matrix is region. 

I have a many-many relationship with relation with year column of Calendar with the year of Sales. 

I have enabled filtering only in one direction from Calendar to Sales. 

Sales doesn't have the rows for 2024 or 2022. 

 

When I put a slicer for year from the Calendar table, 

then the total revenue per region doesn't change no matter which year I select. It always gives me the total revenue for 2023. 

However, when I use the year from the Automation Forecast table as slicer,

it does give me blank for year 2022 or 2024 which is what is expected. 

 

Why is the year slicer from Calendar table giving me the wrong values? 

 

Thank you in advance. 

Need Help
Show and Tell

4 Replies

  • PijushRoy's avatar
    PijushRoy
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi afaro 

    Please check the Year column datatype in both table 

    when I use the year from the Automation Forecast table as slicer
    Is the Automation Forecast table 3rd table in your data model, please check the filter

    • afaro's avatar
      afaro
      Icon for Helper III rankHelper III
      2 years ago

      I made a typing error. 

      The Sales and Automation Forecast table are the same table. Mistakenly used different names for the same table. I don't see an option to edit my post, I would have edited it now if not for that.

      • PijushRoy's avatar
        PijushRoy
        Icon for Community Champion rankCommunity Champion
        2 years ago

        Ok, no problem afaro 

        If the data type is the same for both tables, this should work.
        Also check if you turn off interaction or not

         

        If not solved, 
        Please create a pbix that contains sample data but still reflects your semantic model (tables, relationships, calculated columns, and measures). Upload the pbix to OneDrive, Google Drive, or dropbox and share the link in this thread. If you are using a spreadsheet to create/import the sample data instead of the manual import method, share the spreadsheet as well. 

        Let me know if that works for you


        If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.

        Thanks
        Pijush
        Linkedin