Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
I feel like I'm overlooking something really obvious just because I've been pounding my head on it so long. Any insight? TIA!
Solved! Go to 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.
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
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.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |