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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

How to fill value for empty date to keep continuous same period

I have a line chart and a matrix table, and want to keep the X axis align with table column header to compare data. So I have to fill values for empty date. But there is a problem. Hope someone can help. Thanks.

I have two sample data to compose two visuals:

the first table to compose line chart: sheet1:

WX20191113-174203@2x.png

 the second table to compose the matrix table: sheet2:

WX20191113-174221@2x.png

 here is the final visuals:

WX20191113-174139@2x.png

Here is table relationship(I also created two mapping table for date and brand in order to interact two charts together):

pic1.png

 

Based on table 1, because not every day has full data for each brand/keyword. To keep two visuals align at X axis, I created measure for line value as below:

Value_measure = CALCULATE(IF(ISBLANK(CALCULATE(SUM(Sheet1[Value]),FILTER(ALLSELECTED(Sheet1[Brand]),1=1))),0,CALCULATE(SUM(Sheet1[Value]))))

 

But the problem is:

Brand1 should only have keyword1,2,3; Brand2 should only have keyword 4,5,6.

But in the line chart, the "legand" shows 6 keywords if I selected Brand1 on left slicer. How should I only show filtered keywords in line chart?

The main purpose is only show last 6 days data, and compare two chart values.

Here is the sample data and PBI file:

https://pan.baidu.com/s/1fJY65WvpTVr23h9trR0ntA

Download code is "wy9h"

 

0 REPLIES 0

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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