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)

     

     

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.