Help with DAX for Matrix Visual - Filtering Previous Weeks

[lennelei]

Despite extensive research, I'm stuck on some details regarding how to display data for previous weeks after selecting a single week in a slicer.

I have found solutions that work well for a Column Chart (Histogram), but I am still having difficulties implementing this in a Matrix visual.

Data Model & Setup

My simplified data model consists of three main tables (pbix file here : tests_s3.pbix😞

• Calendar Table (time): Contains the week number and a rank column used to handle week ordering (especially across years, though not explicitly used in my sample).

• Fact Table (fact): Contains the measures (val) per id and per week.

• Dimension Table (dim): Contains id and name.

To manage the slicer selection, I have created a second, disconnected Week Table (date_picker) which is used as the slicer source.

DAX Measures - Two Approaches

I've tried two methods for my measures:

Method 1: All-in-One Measure

MMeasure val S-3 to S = 
VAR WeekRank = MAX(time[rank])
VAR MinRank = MAX(date_picker[rank]) - 3
VAR MaxRank = MAX(date_picker[rank])
RETURN IF(ISINSCOPE(time[week]),
    IF(WeekRank >= MinRank && WeekRank <= MaxRank,COALESCE([Measure val],0),BLANK()),
    CALCULATE( 
        [Measure val],
        time[rank] >= MinRank, 
        time[rank] <= MaxRank 
       )
)

Results with Chart: This works well.

Results with Matrix: This works fairly well, except for the totals. As shown below, I'm unable to get a zero (0) for Store D's total.

Method 2: Basic Measure + Visual Filter

This approach uses a basic measure and tries to filter the visual using a separate measure.

Basic Measure:

Measure val default = COALESCE(SUM(fact[val]),0)

Filter Measure (used as a Visual Filter):

Filter S-3 to S = 
VAR WeekRank = MAX(time[rank])
VAR MinRank = MAX(date_picker[rank]) - 3
VAR MaxRank = MAX(date_picker[rank])
RETURN IF(WeekRank >= MinRank && WeekRank <= MaxRank,1,0)

Results with Chart: This works well.

Results with Matrix: This method does not work at all on the Matrix visual; the filter measure breaks the result (without the filter measure, I've the correct data but for all the weeks of course).

Specific Need

I need a reliable DAX solution that correctly calculates the aggregated value (sum of val) for the selected week AND the three preceding weeks (S-3 to S), and works correctly in a Matrix visual (including correct subtotals/grand totals that show 0 when no data exists, like for Store D).

Any help or suggestions would be greatly appreciated!

[lennelei · Accepted solution]

Gemini 3 came to my rescue 🙂

First of all, it gave me a correct measure for me to achive what I was looking for in a matrix:

Measure val S-3 to S v2 = 
VAR WeekRank = MAX(date_picker[rank])
VAR MinRank = WeekRank - 3
VAR MaxRank = WeekRank
VAR MinContext = MIN(time[rank])
VAR MaxContext = MAX(time[rank])
VAR IsInWindow = MinContext <= MaxRank && MaxContext >= MinRank

VAR Result = CALCULATE([Measure val],time[rank] >= MinRank,time[rank] <= MaxRank)

RETURN IF(IsInWindow,
        COALESCE(Result, 0), 
        BLANK()
)

But more important, it introduced me to the Calculation Groups in Power BI which is a killer feature for my needs as I'll be able to define this calculation method once and reuse it with multiple measures.

[Greg_Deckler]

@lennelei Your best bet may be to use a Complex Selector in your Matrix Visual Filter area. If you can provide sample data could probably be more specific.

The Complex Selector - Microsoft Fabric Community

[lennelei]

Hi,

I did look to your link and sample pbix file. This is very close to what I did and there is the same issue in the Matrix: totals are not calculated per rows (because there is no Weeknum I presume).

In my own report, I can have totals per rows with the all in one measure for example:

Measure val S-3 to S = 
VAR WeekRank = MAX(time[rank])
VAR MinRank = MAX(date_picker[rank]) - 3
VAR MaxRank = MAX(date_picker[rank])
RETURN IF(ISINSCOPE(time[week]),
    IF(WeekRank >= MinRank && WeekRank <= MaxRank,COALESCE([Measure val],0),BLANK()),
    CALCULATE( 
        [Measure val],
        time[rank] >= MinRank, 
        time[rank] <= MaxRank 
       )
)

But I cannot find a way to have zeros instead of blank in this total column.

I can give you the Power Query used to generate sample data if you don't want to download the tests_s3.pbix :

//time
let
 Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Jc25EQAwCAPBXogd+H9qYei/DZ/lAGYjnbsVS1wkt4qq1FCTOurSQEOaaEoLLWmjLR10pJLfdP5W5nfKC/EiLg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [week = _t, rank = _t]),
 #"Type modifié" = Table.TransformColumnTypes(Source,{{"week", Int64.Type}, {"rank", Int64.Type}})
in
 #"Type modifié"

//fact
let
 Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY45DsAgDAT/4poi5uYtiILk/3+IrxilsOTRLKznhAgBNk2HFZRumiqUzA2hTNtDg0WwWFRltWhz4mgSat9DJ05mp1PYf8d0c5oc9kt0Yqe34GWyHdzeiGiWStYL", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [week = _t, id = _t, val = _t]),
 #"Type modifié" = Table.TransformColumnTypes(Source,{{"week", Int64.Type}, {"val", Int64.Type}})
in
 #"Type modifié"

//date_picker
let
 Source = time,
 MinWeek = List.Min(fact[week]),
 MaxWeek = List.Max(fact[week]),
 Filtre = Table.SelectRows(Source, each [week] >= MinWeek and [week] <= MaxWeek)
in
 Filtre

//dim
let
 Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUXJ0dFSK1YlWSgKynZzAzGQg0xkEwLwUIM8FCJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, name = _t]),
 #"Type modifié" = Table.TransformColumnTypes(Source,{{"id", type text}, {"name", type text}})
in
  #"Type modifié"

[lennelei]

Thank you for your fast response! I'll have a look to your link. 

There should be a pbix file in my first message with very simple data. Can't you access it?