Forum Discussion
Filter a card using a table
I know there's a few posts around this but I can't follow them in relation to my own data structure.
I have this page in my report - if I click on the table rows it filters the graph but not the card. I want it to filter the card as well - so, for example, it shows the total number of activities where adults attended.
The DAX for the card is
Here's the relevant section of the model, with all relationships active (LogID between the first two, then attendance type for the latter) :
The issue was that some activities had an attendance of 0 so I needed to explicit about filtering that out:
Total Activities with Filtering =
// Check whether there is filtering on attendance groups
VAR _NoSelection =
(ISBLANK(SELECTEDVALUE(attendance_groups_DIM[AgeGroup])) &&
ISBLANK(SELECTEDVALUE(attendance_groups_DIM[TypeGroup])))RETURN
CALCULATE(
[Total Activities],
// If there is filtering, count activities only where the count of attendances for the relevant group is not 0
// It's not strictly necessary to wrap "UnpivotedAttendances_FACT[Value] <> 0" in an IF statement, but without it, it won't count activities with 0 attendance (and it looks like there is one such activity)
FILTER(UnpivotedAttendances_FACT,
IF(_NoSelection = FALSE,
UnpivotedAttendances_FACT[Value] <> 0,
TRUE())
))
4 Replies
The issue was that some activities had an attendance of 0 so I needed to explicit about filtering that out:
Total Activities with Filtering =
// Check whether there is filtering on attendance groups
VAR _NoSelection =
(ISBLANK(SELECTEDVALUE(attendance_groups_DIM[AgeGroup])) &&
ISBLANK(SELECTEDVALUE(attendance_groups_DIM[TypeGroup])))RETURN
CALCULATE(
[Total Activities],
// If there is filtering, count activities only where the count of attendances for the relevant group is not 0
// It's not strictly necessary to wrap "UnpivotedAttendances_FACT[Value] <> 0" in an IF statement, but without it, it won't count activities with 0 attendance (and it looks like there is one such activity)
FILTER(UnpivotedAttendances_FACT,
IF(_NoSelection = FALSE,
UnpivotedAttendances_FACT[Value] <> 0,
TRUE())
))Hi HollyMusgrove,
There are few possibilities that your card visual is not getting filtered.
- Edit interactions is turned off between visuals:- check and enable the same from Format → Edit interactions
- Could be a relationship direction issue which is not filtering the visual propely, check your filter direction, choos the right direction using Crossfilter() function or if there are multiple relationship ships use the correct one using userelationship() function
- Inactive relationship or diconnected table check for the same
Relationships are not clear from the screenshot you shared.
if nothig works then share the sample data so i can investigate further.
🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!Hi HollyMusgrove,
This is just based on the images and the DAX measures you have shared, it looks like the relationship between Activity_info_fact and UnpivotedAttendances is unidirectional and the fact table is filtering attendance table. On the visual, when you filter the table, the card doesn't get filtered due to the unidirectional relationship.
I hope this solves your problem, if not, can you please share a full view of the model and visual table.If my answer helped you solve the problem, please consider accepting it as the solution and help other members to use the same solution in same/similar situations.
HollyMusgrove Hey,
Your card isn’t responding to filters because the SUM isn’t wrapped in a context-sensitive function. Try this adjusted measure:Total Activities = CALCULATE( SUM(activity_info_FACT[CountInTotals]), CROSSFILTER(attendance_info_FACT[LogID], activity_info_FACT[LogID], BOTH) )Try this measure, let me know if this solve your problem.
Thanks
Harish M
Give kudos if I solve your problem. Accept this as solution.
