Forum Discussion
Running Line chart based on Time slicer
Hello All,
I need to create a line chart for below data with Time on Axis and NoOfPeople on Values:
Camera NoOfPeople Time ID Cam 1 10 10:10 AM 1 Cam 1 3 10:15 AM 2 Cam 1 44 10:20 AM 3 Cam 1 2 10:25 AM 4 Cam 1 3 10:30 AM 5 Cam 1 55 10:35 AM 6 Cam 1 4 10:40 AM 7 Cam 1 44 10:45 AM 8 Cam 1 33 10:50 AM 9
Problem Statement:
I have a line chart and a slicer in which Time is selected
If i choose 10:25 AM in my Time Slicer i should only see data plotted in Line Chart till 10:25 AM i.e. Last 5 entries are not plotted.
Similarily if choose 10:15 AM in my Time Slicer i should only see data plotted for 1st two entries and so on.
Can anybody suggest any trick for doing this?
Prateek Raina
Hi prateekraina,
Based on my test, you should be able to follow steps below to get your expected result.
1. Add a new table with all possible Time value, and make sure there is no relationship between the two tables.
2. Use the formula below to create a new measure.
Measure = CALCULATE ( SUM ( Table1[NoOfPeople] ), FILTER ( Table1, Table1[Time] <= MAX ( Table2[Time] ) ) )3. Then you should be able to show the Time column from your original table as Axis, and the measure as Value on Line Chart to get your expected result.
Here is the sample pbix file for your reference. :smileyhappy:
Regards
2 Replies
Hi prateekraina,
Based on my test, you should be able to follow steps below to get your expected result.
1. Add a new table with all possible Time value, and make sure there is no relationship between the two tables.
2. Use the formula below to create a new measure.
Measure = CALCULATE ( SUM ( Table1[NoOfPeople] ), FILTER ( Table1, Table1[Time] <= MAX ( Table2[Time] ) ) )3. Then you should be able to show the Time column from your original table as Axis, and the measure as Value on Line Chart to get your expected result.
Here is the sample pbix file for your reference. :smileyhappy:
Regards
Thanks a lot for this v-ljerr-msft.
