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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Show full data history on play-axis

I am working on an impact bubbe chart (custom visualization as shown in here: https://blog.pragmaticworks.com/power-bi-custom-visuals-impact-bubble-chart) in order to show the development of different projects over time. 

 

I am getting my data from a sharepoint list and it's set up like this: 

Project Name   l    Modified   l   Data1   l   Data2 

 

"Modified" will be different for the different "Project Names", but can also be the same value in some cases. 

So  my table e.g. looks like this: 

 

Project NameModifiedData1Data2
Project101/03/202010020
Project102/05/20209030
Project105/07/20208050
Project202/05/20202070

 

Currently when I set the play axis to "Modified" the bubble chart only shows the Projects that were modified at the date the play axis is set respectively. What I would like to achieve is to show all projects at every time step of the play axis so one can see the whole picture of the project development. So in the example that Project 2 will also be shown at time step 05/07/2020. 

 

Can someone help? 

Thank you in advance!

 

1 ACCEPTED SOLUTION

Hi @Anonymous 

I think you may want to use conditional formatting function in this visual. However if you add legend into this visual , this function may be not available.

Your demand is a good idea, while it is not supported to implement in Power BI currently.
You can vote up this idea for this function:Conditional formatting for scatter plots
Or you can submit a new idea to improve the Power BI.
It is a place for customers provide feedback about Microsoft Office products . What’s more, if a feedback is high voted there by other customers, it will be promising that Microsoft Product Team will take it into consideration when designing the next version in the future.

I build a new scatter visual , use Measure X,Y axis and slicer to achieve your goal.

Add a new slicer table named Project.

1.png

Measures:

M.Date1 = CALCULATE(SUM('Table'[Data1]),FILTER('Table','Table'[Project Name]=SELECTEDVALUE(Project[ProjectName])))
M.Date2 = CALCULATE(SUM('Table'[Data2]),FILTER('Table','Table'[Project Name]=SELECTEDVALUE(Project[ProjectName])))
Project = IF(HASONEFILTER(Project[ProjectName]),MAX(Project[ProjectName]),"")
Color = IF(MAX('Table'[Modified]) = MAXX(FILTER(ALL('Table'),'Table'[Project Name]=SELECTEDVALUE(Project[ProjectName])&&'Table'[Modified]<=MAX('Table 2'[Modified])),'Table'[Modified]),1,0)

Then use M.Date1 and M.Date2 to replace Date1 and Date2, remove project name in legend as well.

New visual:

2.png

Then add color measure into data color.

3.png

Result is as below. I select Project 1 and Max date = 2020/05/07. So only the value which equal to or the biggest one less than max data will show blue, others will show grey.

4.pngYou can download the pbix file from this link: Show full data history on play-axis

 

Best Regards,

Rico Zhou

 

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

5 REPLIES 5
amitchandak
Super User
Super User

@Anonymous , You can put only one dimesion value on play axis. You can create combine date and project id column and use that

Anonymous
Not applicable

Hi @amitchandak , 

 

Thank you for your answer. 

Could you please elaborate? I am not sure how that will solve my issue as I only want the time on the x-axis.

Hi @Anonymous 

I think you couldn't show all values before by play axis. When I move play axis to 2020/05/07, it will only show the value in 2020/05/07. You may try slicer and measure to achieve your goal.

My sample table is like yours and I add a value column to add into size.

1.png

My measure:

Measure = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Modified]<=MAX('Table 2'[Modified])))
Measure 2 = MAX('Table 2'[Modified])

Build scatter chart as below, build a slicer and a card visual. Then group these three visuals.

2.png

3.png

You can download the pbix file from this link: Show full data history on play-axis

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

 

 

 

Anonymous
Not applicable

Hi @v-rzhou-msft , 

 

Thanks a lot for the detailed explanation, it was very helpful. It does work, however for one particular project I would like to only show the most recent data value and the previous development should be shown as a shadow, so it becomes clear what the current status of a project is. Is that possible? 

 

Best, 

Lone

Hi @Anonymous 

I think you may want to use conditional formatting function in this visual. However if you add legend into this visual , this function may be not available.

Your demand is a good idea, while it is not supported to implement in Power BI currently.
You can vote up this idea for this function:Conditional formatting for scatter plots
Or you can submit a new idea to improve the Power BI.
It is a place for customers provide feedback about Microsoft Office products . What’s more, if a feedback is high voted there by other customers, it will be promising that Microsoft Product Team will take it into consideration when designing the next version in the future.

I build a new scatter visual , use Measure X,Y axis and slicer to achieve your goal.

Add a new slicer table named Project.

1.png

Measures:

M.Date1 = CALCULATE(SUM('Table'[Data1]),FILTER('Table','Table'[Project Name]=SELECTEDVALUE(Project[ProjectName])))
M.Date2 = CALCULATE(SUM('Table'[Data2]),FILTER('Table','Table'[Project Name]=SELECTEDVALUE(Project[ProjectName])))
Project = IF(HASONEFILTER(Project[ProjectName]),MAX(Project[ProjectName]),"")
Color = IF(MAX('Table'[Modified]) = MAXX(FILTER(ALL('Table'),'Table'[Project Name]=SELECTEDVALUE(Project[ProjectName])&&'Table'[Modified]<=MAX('Table 2'[Modified])),'Table'[Modified]),1,0)

Then use M.Date1 and M.Date2 to replace Date1 and Date2, remove project name in legend as well.

New visual:

2.png

Then add color measure into data color.

3.png

Result is as below. I select Project 1 and Max date = 2020/05/07. So only the value which equal to or the biggest one less than max data will show blue, others will show grey.

4.pngYou can download the pbix file from this link: Show full data history on play-axis

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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