Forum Discussion

Data_Scrubber's avatar
Data_Scrubber
Icon for Helper I rankHelper I
2 years ago
Solved

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

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 expecte...
DAX filters
  • lbendlin's avatar
    lbendlin
    2 years ago

    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)

     

     

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago
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's avatar
Data_Scrubber
Icon for Helper I rankHelper I
2 years ago

<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's avatar
    Data_Scrubber
    Icon for Helper I rankHelper I
    2 years ago

    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's avatar
      lbendlin
      Icon for Super User rankSuper User
      2 years ago

      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's avatar
        Data_Scrubber
        Icon for Helper I rankHelper I
        2 years ago

        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.