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

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.
    • 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.