Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |