Forum Discussion

UmairKamal's avatar
UmairKamal
Icon for Helper I rankHelper I
6 months ago
Solved

Field Parameters + multiple calendar systems: how to filter a cohort matrix by a single selected yea

Dear Users,

 

I’m building a cohort-style matrix in Power BI using Field Parameters to switch between two calendar systems:

  • Financial Year (Apr–Mar)
  • Reporting Year (Oct–Sep)

The matrix axis switching works perfectly, but I’m stuck on how to filter the matrix to a single selected year without rewriting measures.

 

Model setup (simplified)

Date table: Calendar_Warrants

  • Date
  • FinancialYear
  • FinancialMonth
  • ReportingYear
  • ReportingMonth

Field Parameters

  • Year parameter
    • FinancialYear
    • ReportingYear
  • Month parameter
    • FinancialMonth
    • ReportingMonth

Calendar type slicer

  • Financial Year
  • Reporting Year

This slicer filters the field parameters, so:

  • Financial mode → FinancialYear + FinancialMonth
  • Reporting mode → ReportingYear + ReportingMonth

I want to add a Year Slicer on the page, lets call it Slicer X, which effectively shows the year values from the Calendar_warrants table depending on what the user has selected from the Calendar Type Slicer.

for example, if the user has selected Financial year from the Calendar Type Slicer, then Slicer X shows the financial year values taken from the Calendar_Warrants Table,

 

and 

 

if the user has selected Reporting year from the Calendar Type Slicer, then Slicer X shows the Rerpoting year values taken from the Calendar_Warrants Table.

this could be done with bookmarks but i was hoping to solve this wihtout changing my existing measures or bookmarks. 


 

 

 

 

if there is not a simple solution available, then I will resort to making bookmarks. 

 

kind regards.

 

Need Help
  • UmairKamal's avatar
    UmairKamal
    6 months ago

    Thanks for finding the workaround. I have found a simpler way by creating another slicer  and using the default functyion of show values of the selected field.

5 Replies

    • UmairKamal's avatar
      UmairKamal
      Icon for Helper I rankHelper I
      6 months ago

      I am afraid this is not what I am looking for. but thanks for responding.

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    6 months ago

    Hi UmairKamal 

    Without using measures, a workaround is to use a table that contains all the categories and their corresponding values. This pattern combines multiple categories into a single table with columns for type, value, and sort order. As the months have different sort orders and Power BI does not accept multiple sort values, this must be enforced using Tabular Editor. This table then needs to be related the actual calendar table in a many-to-many relationship

     

    Please see the attached pbix.

    • UmairKamal's avatar
      UmairKamal
      Icon for Helper I rankHelper I
      6 months ago

      Thanks for finding the workaround. I have found a simpler way by creating another slicer  and using the default functyion of show values of the selected field.

      • v-achippa's avatar
        v-achippa
        Icon for Community Support rankCommunity Support
        6 months ago

        Hi UmairKamal,

         

        Thank you for reaching out to Microsoft Fabric Community.

         

        Thank you FBergamaschi and danextian for the prompt response.

         

        Thank you for the response and confirming that the issue is resolved now. Thank you for being part of Microsoft Fabric Community.

         

        Thanks and regards,

        Anjan Kumar Chippa