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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

Making the page filter show all records from accumulated totals using standard code for accumulating

I have a graph that is showing accumulated totals as generated by:

``````Received_Accumulated =
CALCULATE (
SUM ( 'ExportedRecords'[Received_Counter] ),
FILTER(ALL ( 'Date Dimension'),
'Date Dimension'[Date] <= (MAX('Date Dimension'[Date]))
))``````

This works for the graph, but I also have a data grid on the same page which should show the list of all records that are included in the totals for recieved POs.  When I select one of the weeks in the accumulation graph, I want to see all records which compose that total, but it only shows me the records which transacted on the selected week.  How do I change this behavior?

7 REPLIES 7
Community Support

Hi, @Ocoder

Based on your description, I created data to reproduce your scenario.

Date Dimension:

``Date Dimension = CALENDARAUTO()``

There is no relationship between two tables.

You may create calculated columns and measures as below.

``````Calculated column:
Weeknum1 = WEEKNUM('Date Dimension'[Date])
Weeknum2 = WEEKNUM(ExportedRecords[Date])

Measure:
Received_Accumulated =
CALCULATE(
SUM(ExportedRecords[Received_Counter]),
FILTER(
ALL('ExportedRecords'),
'ExportedRecords'[Date]<=MAX('Date Dimension'[Date])
)
)
Visual control =
IF(
ISFILTERED('Date Dimension'[Weeknum1]),
IF(
WEEKNUM(SELECTEDVALUE(ExportedRecords[Date]))<=SELECTEDVALUE('Date Dimension'[Weeknum1]),
1,0
),
1
)
``````

Finally you need to put the 'Visual control' in the visual level filter of the table visual to display the result.

Week = 2:

Week = 3:

Best Regards

Allan

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

Helper I

This doesn't seem to work for me.  I have also tried putting the visual control marker as a calculated column, but still no.  I can't get the list to filter based on whether visual filter is 1 or 0 and if I have a test visual that counts up the visual control filter totals, it still only adds up the current week.

Community Support

Hi, @Ocoder

Could you share some sample data and expected result with OneDrive for Business? Do mask sensitive data before uploading. Thanks

Best Regards

Allan

Helper I

Thanks for your help and attention to my question.

My task has changed such that this topic is no longer relevant to completion.  I can't spend more time on it at the moment, so I will have to leave this as it stands.

Helper I

@v-alq-msft Thanks for the help.  I am implementing these changes now.  I have one question though, why did you say to create calculated column Weeknum2 when it doesn't seem to be used anywhere?  And I assume you mean that those calculated columns go into the tables they refer to respectively.

EDIT:  Nevermind, I see that you're using Weeknum2 in the visual.

Community Support

Hi, @Ocoder

'Weeknum2' is unimportant. It is just used to show the week num in the table visual for better understanding.

Best Regards

Allan

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

Post Prodigy

@OcoderMaybe you can use the edit interactions feature to stop the chart from filtering the tabular data and separately render the tabular data up to the desired date.

https://docs.microsoft.com/en-us/power-bi/service-reports-visual-interactions

Helpful resources

Announcements

New forum boards available in Real-Time Intelligence.

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

Power BI Monthly Update - May 2024

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

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors