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
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-Forum/ba-p/963216

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/1447523

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

    Apologies for the poor quality of my post.

     

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

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      2 years ago

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

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