topic Re: Power BI - issue with count in Pie Chart in DAX Commands and Tips

Power BI - issue with count in Pie Chart

andi2333 — Thu, 20 Jun 2024 07:52:11 GMT

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!

Re: Power BI - issue with count in Pie Chart

Shivu-2000 — Thu, 20 Jun 2024 09:14:26 GMT

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:

Drag "Date" and "Error" fields to the "Fields" pane in your pie chart.
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!

Re: Power BI - issue with count in Pie Chart

Anonymous — Fri, 21 Jun 2024 06:52:33 GMT

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