Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
twintrbl
Advocate I
Advocate I

Removing drillthrough filters in a measure

I've been beating my head on the bricks on this one... I have one tab with a table of contracts to be renewed. You pick one and drillthrough to a details tab. On the details tab, it shows details for that one contract, BUT I also need to show some metrics for the total Account and the total Account Group.  (Within the total company, there are many Account Groups. Each Account Group has multiple Accounts, and each Account can have multiple contracts, and I need to see performance metrics at the individual Contract, Account, and Account Group on the same page.)  Since it's a drillthrough, the Contract filter is pulled in automatically. So I need to have measures that can remove the contract filter, but still keep the Account filter (based on the Account from the contract), and measures that keep the Account Group (but not the account or contract filter).  I've been trying things like

 

CALCULATE(

     SUM( 'ContractPayments'[Paid]),

     ALL('Accounts'[Account Group])
)

But this still gives me only the single contract, not the whole Account Group.  So I tried to force it to only have a filter on the Account Group with:

 

CALCULATE(

     SUM ( 'ContractPayments'[Paid]),
     FILTER('Accounts', 'Accounts'[Account Group] = SELECTEDVALUE('Accounts'[Account Group]))
)

But it still only showed me the single contract. So it's keeping the filter context of the page from the drillthrough, but not allowing me to filter to just the Group based on the selection. So then I tried to hard code the Account Group just to see if that would work instead of using the SELECTEDVALUE function.  No luck. Same exact result - shows the single contract.

 

So then I tried this:

CALCULATE(
    SUM( 'ContractPayments'[Paid]),
     ALLEXCEPT('Accounts','Accounts'[Account Group])
)
 
And that gives me the entire company (i.e. it removes *all* the filters, not just the Account Group Name).
 
There's something fundamental I'm missing, obviously. I've tried adding extra filters both above and below the ALLEXCEPT statement. I've tried combinations of FILTER and REMOVEFILTERS and ALL and ALLEXCEPT, but I either get a result that shows only the contract (meaning it is keeping the page drillthrough filter and ignoring everything else I'm trying to do) or it's showing the whole company (meaning once I remove any part of the filter context, I can't have any other filter in effect).
 
Can anyone shed some light on what I'm overlooking?  I thought the whole point of CALCULATE was to be able to specify the *exact* filter you want for that measure, regardless of any other filter context - filters on the page, drillthrough, visual, etc...  But no matter what combination of filters I try to set within the CALCULATE statement, there are only two results - it keeps the drillthrough filter, or it drops that filter and shows me the entire company and refuses to see any other filters. 

 

I feel like I'm overlooking something really obvious just because I've been pounding my head on it so long. Any insight? TIA!     

1 ACCEPTED SOLUTION

Hi @az38 ,

 

I tried to reply earlier, but it didn't seem to save, so I'm going to try again...  The ALL('Accounts') filter will return the table with *all* rows. I don't want to remove all the filters - just some of them. I need to keep the filter on Account Group, but drop the one on Account Name. Every combination of ALL, FILTER, ALLEXCEPT, etc either removed none of the filters or removed all of them.

 

However... for the benefit of anyone else who might find this later, I think I figured out the problem. I had my drillthrough filter set to False for "Keep All Filters". Since it was drilling on a third column (Policy Number), it wasn't "reading" that as a filter on Account or Account Group (even though it was implicitly filtering on that by filtering on the Policy Number, since there's only one Account and only one Account Group for any given Policy). So when I tried to remove everything except the Account Group that was implicitly filtered, it just removed everything - since as far as it was concerned, there *was* no filter on Account Group. Just on Policy Number.

 

Once I changed the "Keep All Filters" to true, it carried over a filter for every property for the row I had drilled. Then, when I removed only specific filters with the CALCULATE, it worked.

 

Lesson Learned:  Filters are very literal. If you use a lower level field as a drillthrough filter (and don't keep all your filters on the drillthrough), you can't remove filters that aren't *explicitly* there.

 

View solution in original post

4 REPLIES 4
az38
Community Champion
Community Champion

Hi @twintrbl 

didnt you try just

CALCULATE(
     SUM( 'ContractPayments'[Paid]),
     ALL('Accounts')
)

?

there are two modes of ALL DAX function:

ALL(Table) returns all of the values in the table, removing any filters from the context that otherwise might have been applied.

The ALL(Column) variant is useful when you want to remove the context filters for one or more specific columns and to keep all other context filters.

 

do not hesitate to kudo useful posts and mark solutions as solution
Linkedin


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Hi @az38 ,

 

I tried to reply earlier, but it didn't seem to save, so I'm going to try again...  The ALL('Accounts') filter will return the table with *all* rows. I don't want to remove all the filters - just some of them. I need to keep the filter on Account Group, but drop the one on Account Name. Every combination of ALL, FILTER, ALLEXCEPT, etc either removed none of the filters or removed all of them.

 

However... for the benefit of anyone else who might find this later, I think I figured out the problem. I had my drillthrough filter set to False for "Keep All Filters". Since it was drilling on a third column (Policy Number), it wasn't "reading" that as a filter on Account or Account Group (even though it was implicitly filtering on that by filtering on the Policy Number, since there's only one Account and only one Account Group for any given Policy). So when I tried to remove everything except the Account Group that was implicitly filtered, it just removed everything - since as far as it was concerned, there *was* no filter on Account Group. Just on Policy Number.

 

Once I changed the "Keep All Filters" to true, it carried over a filter for every property for the row I had drilled. Then, when I removed only specific filters with the CALCULATE, it worked.

 

Lesson Learned:  Filters are very literal. If you use a lower level field as a drillthrough filter (and don't keep all your filters on the drillthrough), you can't remove filters that aren't *explicitly* there.

 

az38
Community Champion
Community Champion

So, @twintrbl 

all business logic in DAX is about filtering and relationships, not about math

set, pls, your solution as solution for future usage by other user


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Hi @az38 ,

 

I don't want to return *all* the values in the table. I want it to continue to keep the Account Group filter, but drop the filter on Account Name.  That's the problem. Every variant I've tried for FILTER, ALL, and ALLEXCEPT either continues to filter everything (filter on Account Name and Account Group), or it filters nothing at all and returns the whole table. I can't find a combination that will remove *some* of the filters but leave one of them.

 

Still stumped. 

 

Thanks for the response!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors