Filtering on measure works for stacked column, but not for matrix with exactly the same fields

[MBZA]

Hi all,

I have some data similar to the following:

In practice time goes back to 2014-Q1, and there are more regions.

I have a second disconnected table that I use to populate a slicer on the report, using the below "Fiscal Quarter" column:

Fiscal Quarters = 
VAR curq = YEAR(TODAY()) & "-Q" & QUARTER(TODAY()-60)
VAR quarters = ADDCOLUMNS(DISTINCT(SELECTCOLUMNS('My Table',"Fiscal Quarter",'My Table'[Quarter],"Fiscal Year",'My Table'[Year])), "Fiscal Quarter Sort", -VALUE([Fiscal Year] & RIGHT([Fiscal Quarter],1)))

RETURN FILTER(quarters, [Fiscal Quarter]<=curq)

I am using the slicer to set an effective date, and then I want my chart/matrix to show the most recent 5 quarters based on the selected effective date.

I added the following measures to "My Table":

Effective Quarter = SELECTEDVALUE('Fiscal Quarters'[Fiscal Quarter])

Effective Year = SELECTEDVALUE('Fiscal Quarters'[Fiscal Year])

LastFive = 
VAR curq = SELECTEDVALUE('My Table'[Quarter])
VAR firstq = [Effective Year] - 1 & RIGHT([Effective Quarter],3)
RETURN (curq >= firstq && curq <= [Quarter])*1

Based on the above I have created a column chart, with 'My Table'[Quarter] on the X-axis, Sum of 'My Table'[Column of Interest] on the Y-axis, and 'My Table'[Region] on the Legend. I then add a filter on the visual that LastFive=1. The chart looks as expected:

(I'm using Excel above but the chart correctly looks like this in Power BI)

If I try to do the same with a matrix, it's not working. I put 'My Table'[Region] on rows, 'My Table'[Quarter] on columns, and Sum of 'My Table'[Column of Interest] on values, and have the same visual filter of LastFive=1. The matrix that comes back is completely blank:

If I turn off the filter on LastFive the matrix works, but obviously shows all quarters which is not what I want.

I tried debugging by including LastFive on the values. It shows that it is being calculated correctly:

(Ignore the fact that there are 3 regions above - I just edited a screenshot of the real matrix)

Why does adding LastFive to the matrix filter then make nothing come through?

Thanks in advance.

[Anonymous]

Hi  @MBZA ,

I created some data:

For matrices, you might consider creating measures to compute the values

Here are the steps you can follow：

1. Create calculated table – slicer table.

Date_Table =
CALENDAR(
    DATE(2022,1,1),
    DATE(2024,12,31))

2. Create measure.

Measure =
var _select=SELECTEDVALUE('Date_Table'[Year_Qu])
var _maxdate=MAXX(FILTER(ALL('Date_Table'),'Date_Table'[Year_Qu]=_select),[Date])
var _date=DATE(YEAR(_maxdate)-1,MONTH(_maxdate),DAY(_maxdate))
var _qu=MAXX(FILTER(ALL('Date_Table'),'Date_Table'[Date]=_date),[Year_Qu])
var _mindate=MINX(FILTER(ALL('Date_Table'),'Date_Table'[Year_Qu]=_qu),[Date])
return
SUMX(
    FILTER(ALL('Alltable'),    'Alltable'[Year_Qu]=MAX('Alltable'[Year_Qu])&&'Alltable'[Region]=MAX('Alltable'[Region])&&'Alltable'[Date]>=_mindate&&'Alltable'[Date]<=_maxdate),[Value])

3. Result:

Best Regards,

Liu Yang

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

[MBZA]

Hi @Anonymous, I was able to use measures as you suggest.

However I still want to know why the other approach was not working? Is it a bug? It would be helpful in order to avoid similar issues in future that might not be resolved so simply.