topic Re: Need help quick! Percent On Time measure not working with filters? in DAX Commands and Tips

Need help quick! Percent On Time measure not working with filters?

aallman — Thu, 04 May 2023 17:28:42 GMT

I have a whole page dedicated to exploring our products that have shipped late, meaning there is a page filter for [On Time Ship]="False". I also have a measure to calculate the % on time for each product. The formula is:

% On Time = CALCULATE(DISTINCTCOUNT(CVD_OTD_FTMS_IV[Reference Number]),CVD_OTD_FTMS_IV[On Time Ship]=true(),REMOVEFILTERS(CVD_OTD_FTMS_IV[On Time Ship]))/CALCULATE(DISTINCTCOUNT(CVD_OTD_FTMS_IV[Reference Number]),REMOVEFILTERS(CVD_OTD_FTMS_IV[On Time Ship]))

where [Reference Number] is the unique identifier for someone's ordered item.

This measure isn't working the way I want it to and I am not sure why. The count of late items works as expected, but the total count (or denominator in my % on time measure) doesn't show what I expect unless I remove the page filter for [On Time Ship]="False". I don't know why this would be the case since I have a RemoveFilters command in the measure formula??

For example, the product below shows this with the page filter applied:

and the same product without the page filter applied:

The real total count is 277 for this product. Why is it only 55 when the page filter is applied??

I need help rather quickly, please and thank you!!

Re: Need help quick! Percent On Time measure not working with filters?

aallman — Thu, 04 May 2023 17:33:21 GMT

I also don't know where the 55 is coming from when the page filter is on. I can't figure out which 55 lines are being included and why.

Re: Need help quick! Percent On Time measure not working with filters?

BiNavPete — Fri, 05 May 2023 13:21:08 GMT

Without the PBIX a tricky one, but here goes.

Power BI will work within the context of the row it is looking at.
To remove the context use the ALL or possibly ALLSELECTED Keyword in your calculate measures.
So any measure referring to CVD_OTD_FTMS_IV should wrap ALL(CVD_OTD_FTMS_IV) in it s CALCULATE.

Pete

Re: Need help quick! Percent On Time measure not working with filters?

aallman — Fri, 05 May 2023 16:00:04 GMT

You mean in place of the REMOVEFILTERS part of the formula? I am not sure where to put ALL in this formula:

CALCULATE(DISTINCTCOUNT(CVD_OTD_FTMS_IV[Reference Number]),REMOVEFILTERS(CVD_OTD_FTMS_IV[On Time Ship]))

Re: Need help quick! Percent On Time measure not working with filters?

BiNavPete — Fri, 05 May 2023 16:58:53 GMT

yes in place of the REMOVEFILTERS

Re: Need help quick! Percent On Time measure not working with filters?

aallman — Fri, 05 May 2023 18:12:21 GMT

That didn't work, if I use ALLSELECTED the value becomes 25 just like the late count. If I use ALL then the value becomes 4M which is the total rows I have in my dataset.

Re: Need help quick! Percent On Time measure not working with filters?

BiNavPete — Sun, 07 May 2023 14:40:45 GMT

Not sure where to go next without a PBIX to work with.

Sorry

Pete