Forum Discussion
Help with DAX for Matrix Visual - Filtering Previous Weeks
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.
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Ʃ"