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.

  • 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

    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.

     

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

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