Forum Discussion

GiuseppeMR's avatar
GiuseppeMR
Frequent Visitor
2 years ago
Solved

Dynamically change the Matrix filter

Hi all,

I have a PBIX with 2 entities: Sales and Calendar, and there exists an N-1 relationship between these entities. Moreover, the Sales entity has 2 measures, M1 and M2. On a PBIX page, I would like to define a slicer and a matrix in such a way that the slicer allows selecting the measure (M1 or M2) to show in the matrix.

Here is my issue:

The matrix has a row for each year of the Calendar entity, and when selecting M1, I would like to filter the matrix dynamically, keeping only the current year (i.e., the matrix will have only one row). Hence, I want to apply a filter on the matrix visual.

Any suggestions?

Thanks

Data Protection
  • parry2k's avatar
    parry2k
    2 years ago

    GiuseppeMR first and foremost you need to make some changes to M1. here I have create M1 New that uses the M1 but you can put this logic directly in M1

     

    M1 New = 
VAR __CurrentYear = YEAR ( TODAY () )
RETURN
CALCULATE ( 
   [M1],
   KEEPFILTERS ( 'Calendar'[Year] = __CurrentYear] ) 
)

     

     

    For the selection, you can use a field parameter or calculation groups.

     

     

     

     

     

     

2 Replies

  • parry2k's avatar
    parry2k
    Super User
    2 years ago

    GiuseppeMR first and foremost you need to make some changes to M1. here I have create M1 New that uses the M1 but you can put this logic directly in M1

     

    M1 New = 
VAR __CurrentYear = YEAR ( TODAY () )
RETURN
CALCULATE ( 
   [M1],
   KEEPFILTERS ( 'Calendar'[Year] = __CurrentYear] ) 
)

     

     

    For the selection, you can use a field parameter or calculation groups.

     

     

     

     

     

     

  • NaveenGandhi's avatar
    NaveenGandhi
    Memorable Member
    2 years ago

    Hi GiuseppeMR 

    Please post sample data, expected output along with screenshots to understand the problem better.

    Regards,

    NG