cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

## 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 :

1 ACCEPTED SOLUTION
Frequent Visitor

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 13
Super User

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]
)
)

Frequent Visitor

Unfortunathly still the same result as in the previous screen

Super User

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] )
)

Frequent Visitor

@tamerj1 This measure shows the date next to the values that are not empty

Super User

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]
)
)

Frequent Visitor

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

Frequent Visitor

For info this what the data looks like :

Frequent Visitor

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)

Super User

Would you please place the dax that you have used.

Frequent Visitor

Measure = MAXX (
TOPN (
1,
FILTER (
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]
)

Super User

Measure2 =
MAXX (
TOPN (
1,
FILTER (
ALLSELECTED ( 'kpi TotalErrorHistory' ),
'kpi TotalErrorHistory'[Date] <= MAX ( 'kpi TotalErrorHistory'[Date] )
),
'kpi TotalErrorHistory'[Date]
),
'kpi TotalErrorHistory'[TotalNumber]
)

Frequent Visitor

@tamerj1 Thank you for the suggestion, but this formula replaces the existing totals by max :

Super User

NewMeasure =
MAXX (
TOPN (
1,
FILTER (
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]
)

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors