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
Ocoder
Helper I
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
v-alq-msft
Community Support
Community Support

Hi, @Ocoder 

 

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

f1.png

 

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.

f2.png

 

f5.png

 

Week = 2:

f3.png

 

Week = 3:

f4.png

 

Best Regards

Allan

 

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

 

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.

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

@v-alq-msft 

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.

@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.

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.

sanimesa
Post Prodigy
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
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.