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.
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 😅❤️