Skip to main content
cancel
Showing results for 
Search instead 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

Reply
sajal161292
Helper V
Helper V

filling blanks with previous row in matrix visual

Hi,

 

I have a report as shown below.

 

powerbi7.PNG

 

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
v-yuezhe-msft
Employee
Employee

@sajal161292,


I make a test in the following sample table.
1.JPG

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.
2.JPG

Third, create Matrix visual as below.
3.JPG

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.

View solution in original post

11 REPLIES 11
v-yuezhe-msft
Employee
Employee

@sajal161292,


I make a test in the following sample table.
1.JPG

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.
2.JPG

Third, create Matrix visual as below.
3.JPG

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.

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?

@sajal161292,

Please share sample data of your original table.

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.

Hi,

 

Please find the sample file in the link below:

 

https://drive.google.com/open?id=1Yxc6EX0f7pRoAK_HmlDUwjgS9v37r2LM

 

 

@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.

Hi,

 

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

Is there any alternative approach to achieve this?

@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.

Hi,

 

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

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

@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.

 

Hi,

 

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

@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.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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