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

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

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

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

    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.