Power BI - issue with count in Pie Chart

[andi2333]

Hello,

I have a seemingly simple problem which I cannot solve for whatever reason. I have a file which summarizes "stuck" orders in our supply chain with an error code on a daily level. So the file looks like this.

Date        Ordernumber        Error

17.06       000001                  Article Master Data

17.06       000002                  Customer Master Data

18.06       000001                  Article Master Data

18.06       000003                  Recipe Data

19.06       000003                  Recipe Data

etc.

I have created a measure to calculate the number of stuck orders of "today".

_openTicketsToday = 
VAR _maxDate = MAX('StuckOrders since 2024'[Date])
RETURN
CALCULATE(
    DISTINCTCOUNT('StuckOrders since 2024'[ORDERNUMBER])+0,
    FILTER('StuckOrders since 2024','StuckOrders since 2024'[Date]=_maxDate 
))

Now, the measure standalone works accurate, but when I combine the measure into a pie chart with "Error", I get some super-weird values. Power BI supposedly then counts rows which are not attached to this measure, and I dont know how to fix this.

Thanks for your support!

[Anonymous · Accepted solution]

Thanks for the reply from @Shivu-2000 , please allow me to provide another insight: 
Hi  @andi2333 ,

I created some data:

You can try the following measure.

Measure =
var _table=
FILTER(
    ALL('StuckOrders since 2024'),[date]=MAXX(ALL('StuckOrders since 2024'),[date]))
var _table2=
SUMMARIZE(
    _table,[Ordernumber],[Error])
return
COUNTX(
    FILTER(
        _table2,
        [Error]=MAX('StuckOrders since 2024'[Error])),[Ordernumber])

Best Regards,

Liu Yang

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

[Shivu-2000]

Hi @andi2333,
You are facing this problem because:

Your _openTicketsToday measure specifically filters for orders on the current date using FILTER('StuckOrders since 2024','StuckOrders since 2024'[Date]=_maxDate). However, when you place this measure into a pie chart with "Error," Power BI applies an additional context filter based on the selected error slices in the chart. This can lead to unexpected results because the measure's filter might not align with the chart's filter. 

To solve so you could eithe use the:

1. ALLSELECTED: 

_openTicketsToday =
VAR _maxDate = MAX('StuckOrders since 2024'[Date])
RETURN
CALCULATE(
DISTINCTCOUNT('StuckOrders since 2024'[ORDERNUMBER])+0,
ALLSELECTED('StuckOrders since 2024'), // Consider all selected filters
'StuckOrders since 2024'[Date]=_maxDate // Filter for today's date
)

2. Using DAX Filters in the Visualization:

This approach uses the built-in Power BI filtering options to achieve the desired outcome:

1. Drag "Date" and "Error" fields to the "Fields" pane in your pie chart.
2. In the "Filters" pane, set a filter on the "Date" field to be equal to today's date. This ensures only data for today is displayed.
   
   If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
   Happy to help!