Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
Solved! Go to Solution.
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
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
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
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
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
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
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
97 | |
69 | |
46 | |
39 | |
33 |
User | Count |
---|---|
158 | |
102 | |
60 | |
43 | |
40 |