Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have this sample table
Box# | Item | Category | Date Produced | Truck2 Requested | Truck3 Requested |
B100 | A | Autos | 2/1/2021 | 4 | 0 |
B100 | B | Home | 2/5/2021 | 1 | 1 |
B200 | A2 | Home | 2/8/2021 | 0 | 2 |
B300 | A3 | Autos | 2/12/2021 | 1 | 3 |
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?
Solved! Go to 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))
Proud to be a Super User!
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])
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
If you need a different outcome please include a more representative set of data and a screen cap of the desired result.
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
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))
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.
Hi @SamOvermars ,
Could you provide a sample pbix file and the expected outcome as a visual?
Proud to be a Super User!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |