Forum Discussion
Replace empty values in a matrix with the previous non blank value
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
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
13 Replies
- tamerj1
Community Champion
Hi Anonymous
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]
) - tamerj1
Anonymous
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]
)
)Unfortunathly still the same result as in the previous screen
- tamerj1
Anonymous
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] )
)
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)
- tamerj1
Anonymous
Would you please place the dax that you have used.
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]
)
