Forum Discussion
Relationship/Slicer Issue
One option would be to create a disconnected table with all valid combinations of sub group, family, plant and ANC code, e.g.
Disconnected Table = SUMMARIZE ( sch_fact_material_availability_global, sch_dim_family_global[sub_group], sch_dim_family_global[family], sch_dim_plant_global[plant_code], sch_dim_anc_global[anc_code_old] )You may want to add additional columns with display-friendly names to show to the user, rather than showing codes.
Use this disconnected table for all your slicers so that user can progressively narrow down the list of ANC codes.
Depending on how many measure you need to show in your table visual you could either create individual measures or create a calculation group to apply the filters. A basic measure would be e.g.
Sum of quantity = CALCULATE ( SUM ( sch_fact_material_availability_global[quantity] ), KEEPFILTERS ( TREATAS ( VALUES ( 'Disconnected table'[anc code] ), sch_fact_material_availability_global[anc code] ) ) )I think you need to enable the interactions between slicers and the table visual to get the "select all" option working.
The slicers won't affect the visual directly, as there is no relationship from the disconnected table, but the filters which are created by the slicers need to be available to the measure running inside the table, so that the VALUES function will return the correct results.
Hi Vinicius_Buba,
Based on your requirements you need a DAX measure that creates a reverse lookup pattern
Here's a comprehensive solution:
1-Create a Bridge Table for ANC Relationships
- Create a calculated table that maps all ANC codes to their related subgroups and families:
ANC_Relationships =
SUMMARIZE(
sch_fact_material_availability_global,
sch_dim_family_global[sub_group],
sch_dim_family_global[family],
sch_dim_anc_global[anc_code_old]
)2-Create this measure to control the table filtering:
Filtered Quantity =
VAR SelectedSubGroups =
ALLSELECTED(sch_dim_family_global[sub_group])
VAR SelectedFamilies =
ALLSELECTED(sch_dim_family_global[family])
// Get all ANCs that belong to the selected subgroups/families
VAR RelevantANCs =
CALCULATETABLE(
VALUES(sch_dim_anc_global[anc_code_old]),
TREATAS(SelectedSubGroups, sch_dim_family_global[sub_group]),
TREATAS(SelectedFamilies, sch_dim_family_global[family])
)
// Check if current row's ANC is in the relevant ANCs list
VAR CurrentANC = SELECTEDVALUE(sch_dim_anc_global[anc_code_old])
VAR IsRelevantANC =
NOT ISBLANK(CurrentANC) &&
COUNTROWS(INTERSECT(RelevantANCs, {CurrentANC})) > 0
RETURN
IF(
IsRelevantANC,
SUM(sch_fact_material_availability_global[quantity]),
BLANK()
)
3-If the above doesn't perform well, try this approach:
Filtered Quantity v2 =
VAR SelectedSubGroups =
ALLSELECTED(sch_dim_family_global[sub_group])
VAR SelectedFamilies =
ALLSELECTED(sch_dim_family_global[family])
// Get ANCs from selected context
VAR SelectedANCs =
CALCULATETABLE(
VALUES(sch_dim_anc_global[anc_code_old]),
TREATAS(SelectedSubGroups, sch_dim_family_global[sub_group]),
TREATAS(SelectedFamilies, sch_dim_family_global[family])
)
// Apply filtering using TREATAS
RETURN
CALCULATE(
SUM(sch_fact_material_availability_global[quantity]),
TREATAS(SelectedANCs, sch_dim_anc_global[anc_code_old])
)
4-Finally, Setup Your Report Page:
- Disable Visual Interactions: Turn off all interactions between your slicers and the main data table
- Create Your Table Visual
For Rows: sch_dim_family_global[sub_group], sch_dim_family_global[family], sch_dim_anc_global[anc_code_old], sch_dim_plant_global[plant_code]
For Values: Use the Filtered Quantity measure
- Keep Your Slicers As Is:
- Sub Group slicer (single select)
- Family slicer (multi select filtered by Sub Group)
- Plant Code slicer (multi select filtered by Sub Group/Family)
- ANC Code slicer (multi select filtered by Sub Group/Family)
Note:
- If you get performance issues try creating a physical relationship between the ANC_Relationships table and your fact/dimension tables
- Make sure your sch_fact_material_availability_global table has proper relationships to all dimension tables
- Test with small selections first (to verify the logic works correctly)
Hello Ahmed-Elfeel, thanks a lot for the time and effort coming up with the suggestion - unfortunately, it hasn't worked (it is working just like before, that is, it only shows the correct data table if I individually select ANCs, otherwise it will show all). Just wanted to ask what the role of the ANC_Relationship table is if not used on slicers, fields on the table, or any measures. Maybe I'm missing something here?
Again, thanks a lot!Hi Vinicius_Buba,
The problem here is that when you disable visual interactions the table loses ALL filtering from the slicers So we need to manually recreate the filtering logic in DAX ☺️❤️
Could you please try these approaches: (Corrected)First create and Use the Bridge Table Properly:
ANC_Relationships = SUMMARIZE( sch_fact_material_availability_global, sch_dim_anc_global[anc_code_old], sch_dim_family_global[sub_group], sch_dim_family_global[family] )Now create relationships:
ANC_Relationships[anc_code_old] → sch_dim_anc_global[anc_code_old]
ANC_Relationships[sub_group] → sch_dim_family_global[sub_group]
ANC_Relationships[family] → sch_dim_family_global[family]
Then Create the Proper Filter Measure:
Filtered Quantity = VAR SelectedSubGroups = VALUES(sch_dim_family_global[sub_group]) VAR SelectedFamilies = VALUES(sch_dim_family_global[family]) // Get ANCs from selected subgroups/families using the bridge table VAR RelevantANCs = CALCULATETABLE( VALUES(ANC_Relationships[anc_code_old]), ANC_Relationships[sub_group] IN SelectedSubGroups, ANC_Relationships[family] IN SelectedFamilies ) // Now get ALL subgroups/families that use these ANCs VAR RelatedSubGroupsFamilies = CALCULATETABLE( SUMMARIZE( ANC_Relationships, ANC_Relationships[sub_group], ANC_Relationships[family] ), ANC_Relationships[anc_code_old] IN RelevantANCs ) // Check if current row exists in the related data VAR CurrentSubGroup = SELECTEDVALUE(sch_dim_family_global[sub_group]) VAR CurrentFamily = SELECTEDVALUE(sch_dim_family_global[family]) VAR ShouldShowData = CONTAINSROW( RelatedSubGroupsFamilies, CurrentSubGroup, CurrentFamily ) RETURN IF( ShouldShowData, SUM(sch_fact_material_availability_global[quantity]), BLANK() )If the bridge table approach is too complex, try this direct method (No Bridge Table):
Filtered Quantity Direct = VAR SelectedSubGroups = ALLSELECTED(sch_dim_family_global[sub_group]) VAR SelectedFamilies = ALLSELECTED(sch_dim_family_global[family]) // Get ANCs from current selection VAR SelectedANCs = CALCULATETABLE( VALUES(sch_dim_anc_global[anc_code_old]), sch_dim_family_global[sub_group] IN SelectedSubGroups, sch_dim_family_global[family] IN SelectedFamilies ) // Use these ANCs to filter the entire table RETURN CALCULATE( SUM(sch_fact_material_availability_global[quantity]), KEEPFILTERS(sch_dim_anc_global[anc_code_old] IN SelectedANCs) )Note:
Disable ALL visual interactions between slicers and your data table
In your table visual use the Filtered Quantity measure as your value
Keep your slicers as they are
You Can try the direct method (No Bridge Table) as it the simplest way it will work 😅❤️
if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.
