Show rows of a matrix based on a condition

[Ponn1510]

Hi Experts,

I am trying to create a matrix visual that takes two hierarchy levels - Category1 and Category2 as rows.
Weeknum as columns. Value column is to be shown as value.
I am using a range slicer that limits the columns shown in matrix. Data is something like this

Category1	Category2	WeekNum	Value
Fruits	ABC	202401	155
	DEF	202404	209
	ABC	202352	567
Veggies	DEF	202401	264
	GHL	202353	449
	GHL	202401	699

I am trying to show only rows that have value for selected top range weeknum and show values for previous weeks(columns) only for those rows.

For Example, If Weeknum slicer is selected as 202301 to 202401, Then
selected top range weeknum - 202401
Rows that have data in 202401:

Fruits	ABC	202401	155
Veggies	DEF	202401	264
Veggies	GHL	202401	699

So final expected Matrix should look like

Category1	Category2	202401	202353	202352	...	202301
Fruits	ABC	155	0	567		0
Veggies	DEF	264	0	0		0
Veggies	GHL	699	449	0		0

Kindly help me with ways of achieving this.

I tried different approaches like dax measure for value to be shown, also tried table functions with different filters but unable to show the dax table in matrix.

Help is much appreciated, TIA.

[Sahir_Maharaj · Accepted solution]

Hello @Ponn1510,

Can you please try this approach:

1. Identify Top Range WeekNum

TopRangeWeekNum = 
MAXX(
    FILTER(
        ALLSELECTED('YourDataTable'),
        'YourDataTable'[WeekNum] <= MAX('YourDataTable'[WeekNum])
    ),
    'YourDataTable'[WeekNum]
)

2. Filter Values

FilteredValue = 
VAR TopWeekNum = [TopRangeWeekNum]
RETURN
IF (
    CALCULATE(
        COUNTROWS('YourDataTable'),
        FILTER(
            'YourDataTable',
            'YourDataTable'[WeekNum] = TopWeekNum
        )
    ) > 0,
    SUM('YourDataTable'[Value]),
    BLANK()
)

Hope this helps.