Filtered results not as expected (problems with ALLEXCEPT and REMOVEFILTERS)

[Data_Scrubber]

EDIT:

Sample data + PBIX here- SampleData-Filtering.zip

(Note: numbers inline below were rough approximations for sake of simplicity - key point is that some were above, some were below the expected value)

ORIGINAL:

Hi folks,

I'm having an issue with something that seems conceptually simple 🙂

I'm trying to create a measure that gives me a "grand total" that is filtered in a particular way.

Data in my table:

OrderNumber = a distinct value

Location = "Chicago 1" or "Chicago 2"

ContactInfo = Yes or blank

NumberOfDailyOrders = A calculated column representing the number of orders in a day.

This report page is filtered on ContactInfo = Yes.

ALL report pages are filtered on NumberOfDailyOrders > 3.

If I go into Data view & manually filter the table, here's what I see.

  Just the Location & NumberOfDailyOrders filters = 5000

  Location, NumberOfDailyOrders and ContactInfo filters = 128

I'm trying to create a measure for that first value - it's the "grand total" of orders that I'm trying to track. Then I'll use the count of orders with all 3 filters to calculate the % of the grand total that this represents.  (Example: 128 / 5000 = 2.56% of the orders are associated with ContactInfo)

Things I've tried for the measure that have not worked, based off reading the DAX docs & looking at a lot of suggestions online:

MySliceTotal = CALCULATE(DISTINCTCOUNT('MyTable'[OrderNumber]), FILTER('MyTable', 'MyTable'[Location] = "Chicago 1"))

• This returns 128.

MySliceTotal = CALCULATE(DISTINCTCOUNT('MyTable'[OrderNumber]), ALLEXCEPT('MyTable', 'MyTable'[Location], 'MyTable'[NumberOfDailyOrders]))

• This returns 4800.
• My expectation would be that this removes all filters EXCEPT the two listed...so the value should be 5000.
• Not aware of a way that I could view the Order Numbers/rows that are NOT included here - that might give me a clue as to why those are being filtered out.

MySliceTotal = CALCULATE([CountOfOrders], REMOVEFILTERS('MyTable'), VALUES('MyTable'[Location]))

• CountOfOrders is a measure -> DISTINCTCOUNT('MyTable'[OrderNumber])
• This returns 5100.

MySliceTotal = CALCULATE([CountOfOrders], REMOVEFILTERS('MyTable'), SUMMARIZE('MyTable', 'MyTable'[NumberOfDailyOrders], 'MyTable'[Location]))

• This returns 4800

When I tried this in DAX studio, I get the expected result (5000):

VAR TestMyOrderCount = CALCULATE(COUNTROWS('MyTable'), 'MyTable'[Location] = "Chicago 1", 'MyTable'[NumberOfDailyOrders ] > 3)

• I would expect this to be the same as either the ALLEXCEPT or REMOVEFILTERS/SUMMARIZE options above. So I'm very confused on why it returns the expected value.
• And obviously I couldn't hardcode this into a measure, since those page filters could be changed.

Any thoughts on what I might be doing wrong here? Even a nudge in the right direction would help. I've been digging into this issue for several hours per day, every day this week, and my brain is fried from all of the searches 🙂

[lbendlin]

I'm trying to create a measure that gives me a "grand total" that is filtered in a particular way.

How will you make sure that your users will not be confused?

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Data_Scrubber]

Apologies for the poor quality of my post.

I've now provided sample data + a PBIX that has the filtering I described.

[lbendlin]

MySliceTotal1 =
SUMX (
    FILTER (
        ADDCOLUMNS (
            SUMMARIZECOLUMNS ( 'Start'[Date] ),
            "do", CALCULATE ( [CountOfOrders] )
        ),
        [do] > 3
    ),
    [do]
)

[Data_Scrubber]

Thanks again for your help.

My understanding of what you shared is that it would calculate the distinct # of orders for each date, then filter the results to only show those with a # of orders per date > 3, and then sum the remaining order count.

That seems very straightforward, but does not take into account any filtering being applied to the visual and/or the page itself.

I have updated the file linked in my first post. I added some additional context to what I'm trying to do, and how the visuals will be filtered.

On "page with single page filter", your suggested measure gives the expected result.

- Measure is named MySliceTotal4

On "page with multiple page filters", your suggested measure returns blank/0.

- I definitely expect to see a lower number with an additional filter, but I don't understand why it would be 0.

[lbendlin]

but does not take into account any filtering being applied to the visual and/or the page itself.

It doesn't have to - the filter context is taking care of that.

[Data_Scrubber]

<Having problems posting - think I'm getting flagged as a spammer. 2nd half to this will be in a separate message>

I think one or more of these is true:

• A concept is completely eluding me
• I'm doing a horrible job explaining
• My examples aren't helpful

Thanks for your patience.

What I'm trying to do is remove a page filter from a set of data.

• In the provided sample, I want to remove the ContactInfo filter.

So when I said "it doesn't take into account any filtering", I meant that your proposed measure doesn't do anything like remove existing filters.

That's one obvious difference that explains why your proposed measure shows different results on the two pages in my sample report.

• It shows the expected result when the ContactInfo filter is removed from the page.

[Data_Scrubber]

Had to delete HTML blocks that had the DAX code for MySliceTotal5 and 6.

https://  community.fabric.microsoft.com/t5/Desktop/Removing-Page-level-filter-for-just-1-visualization/m-p/90021 

I did additional searches related to my problem, and tried the suggestion in the thread above (this is MySliceTotal5, in the 0804-1615 version of the PBIX added to the zip in the first post) - still not the expected value.

Looking further at the Microsoft DAX documentation page for "ALL", I'm wondering if perhaps I'm running into the Auto-Exist functionality referenced there?

I copied from the guidance there (MySliceTotal6) - still not correct AND yet another mysterious value for the result 🙂

I could change my approach in the report, and instead of having a page filter for ContactInfo, I could just add that to the relevant visuals instead, and exclude it from the new one I want to add. But that isn't really solving the problem, and now means every time I add new visuals to the page, I need to remember to add the same filter. Seems much cleaner to have it as a page filter, and then do something in my new measure to somehow remove that filter.

[lbendlin]

You would need to show your data model, but adding a REMOVEFILTERS on the ContactInfo column to my CALCULATE may be sufficient. But it may also be confusing to your users.

[Data_Scrubber]

The data model is simple - it's all one table.

Confusing to users - I don't think it will be for 2 reasons

1. This is how the customer asked me to do it 🙂
   1. There's a note on the page explaining why it's only showing data from one site.
   2. The filtering where number of orders > 3 was also a specific request.
2. A user will be able to open the filter pane and see what's applied.

But I DO think that having a hardcoded filter value within the measure itself would be confusing.

I tried modifying the CALCULATE as suggested.

MySliceTotal4 = 
SUMX (
    FILTER (
        ADDCOLUMNS (
            SUMMARIZECOLUMNS ( 'Start'[Date] ),
            "do", CALCULATE ( [CountOfOrders], REMOVEFILTERS('Start'[ContactInfo]) )
        ),
        [do] > 3
    ),
    [do]
)

That returns 2112 (incorrect).

Drilled into this further in the DAX Query View, removing the SUMX so I could see the table data.

DEFINE

VAR MySliceTotal = 
		FILTER (
			ADDCOLUMNS (
				SUMMARIZECOLUMNS ( 'Start'[Date], 'Start'[NumberOfDailyOrders], FILTER('Start', 'Start'[Location] = "Chicago 1")  ),
				"do", CALCULATE( [CountOfOrders],  REMOVEFILTERS('Start'[ContactInfo]))
			),
			[do] > 3			
		)
	
EVALUATE
MySliceTotal ORDER BY [Date] ASC

I included "NumberOfDailyOrders" that I was already calculating, and comparing that to the value in "do". There are many instances where "do" is inexplicably 2x the value of "NumberOfDailyOrders".

Example:

But if that column is showing 2x the value in some cases, I'd expect the result of the SUMX of that column (2112) to be MORE than the expected value (5529). Yet it's less. So there's still some filtering happening that's not clear.

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[Data_Scrubber]

SampleData-Filtering.zip

Sample data in excel file.

Refer to SampleData-Filtering-0805-1430 PBIX for the report with various attempts at these measures, as well as the DAX Query for MySliceTotal that shows the double counting in some scenarios.

[lbendlin · Accepted solution]

Interesting.

lbendlin-0805 = 
CALCULATE (DISTINCTCOUNT('Start'[OrderNumber]),ALLEXCEPT('Start','Start'[Location],'Start'[NumberOfDailyOrders]))

does not work. But 

lbendlin-0805 = 
CALCULATE (DISTINCTCOUNT('Start'[OrderNumber]),all(),'Start'[Location]="Chicago 1",'Start'[NumberOfDailyOrders]>3)

works.  I think it is related to this: Why Power BI totals might seem inaccurate - SQLBI

Here's a compromise:

lbendlin-0805 = 
CALCULATE(countrows(VALUES('Start'[OrderNumber])),all('Start'[ContactInfo]),'Start'[NumberOfDailyOrders]>3)

[Data_Scrubber]

Thanks a ton for drilling in. I'm glad I wasn't the only one seeing the weird behavior! 🙂

Thanks for the link. Read through that multiple times today and experimented with some things, and I still don't think I have a handle on what's really happening, but I'll keep looking at it.

I need the measure to not have that hardcoded value & just use the "on all pages" filter, so I'm going to need to use ALLEXCEPT. Hopefully the light bulb goes on soon.