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
SamOvermars
Helper I
Helper I

Filtering rolling total based on value in another column

Hello, 

I have this sample table 

Box#ItemCategoryDate ProducedTruck2 RequestedTruck3 Requested
B100AAutos2/1/202140
B100BHome2/5/202111
B200A2Home2/8/202102
B300A3Autos2/12/202113

 

I wanted a line chart that will show a rolling total for how many items were produced for lets say truck 2 or truck 3 (where truck requested is not 0)

so I used this dax measure for the value , and applied a filter of truck 2 but still the count seems off

Measure = CALCULATE( COUNTX(FILTER('table','table'[Category] = "Autos"),'table'[Item])
,FILTER( ALLSELECTED( 'table'[Date Produced]), 'table'[Date Produced] <= MAX('table'[Date Produced])))

 

If I go to the table in the data modeling tab and filter Truck2 > 1 and the type "Auto" I will see 90 rows in my original data, but with the line visual the last value was 120.

What im I missing here?

 

 

 

1 ACCEPTED SOLUTION

Hi @SamOvermars ,

 

Maybe you should filter the data in the measure

Measure = CALCULATE( COUNTROWS('table')
,FILTER( ALL( 'table'[Date Produced]), 'table'[Date Produced] <= MAX('table'[Date Produced]) && 'table'[Produced Truck2 Requested]<>0))


I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


View solution in original post

7 REPLIES 7
richbenmintz
Solution Sage
Solution Sage

Hi @SamOvermars,

 

If you replace

ALLSELECTED( 'table'[Date Produced])

 with 

ALL( 'table'[Date Produced])

 does that do the trick, now you might have to add a min date to your filter like

 'table'[Date Produced] >= MIN('table'[Date Produced]) && 'table'[Date Produced] <= MAX('table'[Date Produced])


I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Hi @richbenmintz , Thank you for your response, I tried with that but it started showing me the items count every day in the line chart. which is not what I desired. I needed the rolling total that can be filtered. Seems like the issue I have is with the COUNTX part instead of the date. Just not sure how to tackle it.

 

Hi @SamOvermars ,

 

Using your sample data and assuming you are looking to count the rows cummulatively, the following Measure should work

 

Measure = CALCULATE( COUNTROWS('table')
,FILTER( ALL( 'table'[Date Produced]), 'table'[Date Produced] <= MAX('table'[Date Produced])))

produces the following line chart

richbenmintz_0-1633112547459.png

If you need a different outcome please include a more representative set of data and a screen cap of the desired result.

 



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Thank you @richbenmintz  Almost!It worked but its not letting me filter the visual.

Is there any reason why the line chart looks like this after adding a filter to the visual where truck 2 is not 0

SamOvermars_1-1633540725648.png

 

SamOvermars_0-1633540568779.png

 

Hi @SamOvermars ,

 

Maybe you should filter the data in the measure

Measure = CALCULATE( COUNTROWS('table')
,FILTER( ALL( 'table'[Date Produced]), 'table'[Date Produced] <= MAX('table'[Date Produced]) && 'table'[Produced Truck2 Requested]<>0))


I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


@richbenmintz  Im not sure how Power BI is handeling this in the backend. but the measure is showing it like this now.

SamOvermars_0-1633547462549.png

 

Hi @SamOvermars ,

 

Could you provide a sample pbix file and the expected outcome as a visual?



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


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.