Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello ,
I have this matrix that shows the total number of differenet counts , I need to replace empty values with the previous total as shown in the screens :
Thank you in advance
Solved! Go to Solution.
Thanks @tamerj1 for the answer. It unfortnathly didn't show the expected result .
I finally created Date.WeekOfYear column and the totals were grouped accordinaly , so no emty values were left
This dhould work. You csn replace the outer MAXX with SUMX to sum the rows at total level
Measure1 =
VAR SelectedTable =
ALLSELECTED ( 'kpi TotalErrorHistory' )
VAR CurrentDate =
CALCULATE (
MAX ( 'kpi TotalErrorHistory'[Date] ),
ALL ( 'kpi TotalErrorHistory'[KPIDescription] )
)
RETURN
MAXX (
VALUES ( 'kpi TotalErrorHistory'[KPIDescription] ),
MAXX (
TOPN (
1,
FILTER (
SelectedTable,
VAR KPIDesc = 'kpi TotalErrorHistory'[KPIDescription]
RETURN
'kpi TotalErrorHistory'[Date] <= CurrentDate
&& 'kpi TotalErrorHistory'[KPIDescription] = KPIDesc
),
'kpi TotalErrorHistory'[Date]
),
'kpi TotalErrorHistory'[TotalNumber]
)
)
Would you please place tge following measure in the matrix values and let me know what result do you get?
CALCULATE (
MAX ( 'kpi TotalErrorHistory'[Date] ),
ALL ( 'kpi TotalErrorHistory'[KPIDescription] )
)
Apologies for the late response. I got engaged with something else.
This has to be a dimension table. Create a new calculated table
Dates = ALLNOBLANKROW ( 'kpi TotalErrorHistory'[Date] )
create the relationship between the two tables. Place the new dimension date column in the columns of the matrix instead of tge original one. Tyen try the following measure in the values. Remember you can change the MAXX to SUMX based on your requirement.
Measure1 =
VAR SelectedTable =
ALLSELECTED ( 'kpi TotalErrorHistory' )
VAR CurrentDate =
MAX ( 'Dates'[Date] )
RETURN
MAXX (
VALUES ( 'kpi TotalErrorHistory'[KPIDescription] ),
SUMX (
TOPN (
1,
FILTER (
SelectedTable,
VAR KPIDesc = 'kpi TotalErrorHistory'[KPIDescription]
RETURN
'kpi TotalErrorHistory'[Date] <= CurrentDate
&& 'kpi TotalErrorHistory'[KPIDescription] = KPIDesc
),
'kpi TotalErrorHistory'[Date]
),
'kpi TotalErrorHistory'[TotalNumber]
)
)
Thanks @tamerj1 for the answer. It unfortnathly didn't show the expected result .
I finally created Date.WeekOfYear column and the totals were grouped accordinaly , so no emty values were left
For info this what the data looks like :
Hello @tamerj1 , Thank you for the answer ,However the measure still shows the same results as what I had before ( for your info I replaced Mesure with the max of the total number I had in the column)
Measure = MAXX (
TOPN (
1,
FILTER (
ADDCOLUMNS (
FILTER ( ALLSELECTED ( 'kpi TotalErrorHistory'[Date]), 'kpi TotalErrorHistory'[Date]<= MAX ( 'kpi TotalErrorHistory'[Date] ) ),
"@Value",
VAR CurrentDate = 'kpi TotalErrorHistory'[Date]
RETURN
CALCULATE ( [Measure 2], 'kpi TotalErrorHistory'[Date] = CurrentDate )
),
[@Value] <> BLANK ()
),
'kpi TotalErrorHistory'[Date]
),
[@Value]
)
Please try
Measure2 =
MAXX (
TOPN (
1,
FILTER (
ALLSELECTED ( 'kpi TotalErrorHistory' ),
'kpi TotalErrorHistory'[Date] <= MAX ( 'kpi TotalErrorHistory'[Date] )
),
'kpi TotalErrorHistory'[Date]
),
'kpi TotalErrorHistory'[TotalNumber]
)
please try
NewMeasure =
MAXX (
TOPN (
1,
FILTER (
ADDCOLUMNS (
FILTER ( ALLSELECTED ( 'Table'[Date] ), 'Table'[Date] <= MAX ( 'Table'[Date] ) ),
"@Value",
VAR CurrentDate = 'Table'[Date]
RETURN
CALCULATE ( [Measure], 'Table'[Date] = CurrentDate )
),
[@Value] <> BLANK ()
),
'Table'[Date]
),
[@Value]
)
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
25 | |
21 | |
19 | |
14 | |
11 |
User | Count |
---|---|
43 | |
35 | |
25 | |
22 | |
22 |