Forum Discussion
Help with DAX for Matrix Visual - Filtering Previous Weeks
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!
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.
4 Replies
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.
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?
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é"
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.
