cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper V

## filling blanks with previous row in matrix visual

Hi,

I have a report as shown below.

As shown there is no data from 2018-01 till 2018-11 for second hieracrchy.

In case of any blank,I want to replace it with the data in previous row and extend it till last week so that there is no blank row and there exists data across every week

1 ACCEPTED SOLUTION
Employee

I make a test in the following sample table.

First, add a conditional column in the table.

`NewValue = IF(ISBLANK(Table[Value]), CALCULATE(FIRSTNONBLANK(Table[Value],Table[Value]),FILTER(Table,Table[Custom]=EARLIER(Table[Custom]) && Table[Week]=EARLIER(Table[Week]))),Table[Value])`

Second, create a new columns using DAX below.

Third, create Matrix visual as below.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
11 REPLIES 11
Employee

I make a test in the following sample table.

First, add a conditional column in the table.

`NewValue = IF(ISBLANK(Table[Value]), CALCULATE(FIRSTNONBLANK(Table[Value],Table[Value]),FILTER(Table,Table[Custom]=EARLIER(Table[Custom]) && Table[Week]=EARLIER(Table[Week]))),Table[Value])`

Second, create a new columns using DAX below.

Third, create Matrix visual as below.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper V

Hi,

It is not working correctly for me.

I think you have got confused with the requirement.

Purpose is to fill the weeks having no data as showin in the screenshots with the data of previous week and roll it up across the weeks till there is data for the week.

So can you please suggest an alternative?

Employee

@sajal161292,

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper V

Hi,

Employee

@sajal161292,

In your sample data, I note that you don't have rows in some weeks for specific items, in this case, I am afraid that it is not possible to fill blanks with previous row in Matrix visual.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper V

Hi,

I need to implement it urgently in one of my report.

Is there any alternative approach to achieve this?

Employee

@sajal161292,

You can add rows for these items which have blank values in these weeks(2018-01, 2018-02.....),  then create the DAX I provide in your table. And the  sample table has been provided in my first reply.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper V

Hi,

How can i add blank rows in matrix visual in power BI.

Or do i need to add blank rows in SQL query?

Employee

@sajal161292,

You would need to add these rows in your original data source but not in Martix.

Regards,

Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper V

Hi,

When i have to add blank rows in my SQL query i need to hardcode the quantity for a particular date right?

Employee

@sajal161292,

Yes. Add null values for quantity, for more details, please see the Value column in my first reply.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors