Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
ZR-PBIFirstFirs
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 : 

ZRPBIFirstFirs_0-1684927354175.png

 

ZRPBIFirstFirs_0-1684927674880.png

 

 

Thank you in advance

 

 

1 ACCEPTED 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

View solution in original post

13 REPLIES 13
tamerj1
Super User
Super User

@ZR-PBIFirstFirs 

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

@tamerj1 

Unfortunathly still the same result as in the previous screen

@ZR-PBIFirstFirs 

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

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

@ZR-PBIFirstFirs 

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

ZR-PBIFirstFirs
Frequent Visitor

For info this what the data looks like : 

ZRPBIFirstFirs_0-1684929310614.png

 

ZR-PBIFirstFirs
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)

@ZR-PBIFirstFirs 

Would you please place the dax that you have used. 

@tamerj1 

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

@ZR-PBIFirstFirs 

Please try

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

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

ZRPBIFirstFirs_0-1684933062771.png

 

tamerj1
Super User
Super User

Hi @ZR-PBIFirstFirs 

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.