Relationship/Slicer Issue

[Vinicius_Buba]

1. Goal
My goal is to create an interactive report page where a user can select a subgroup and family and see not only the data for those selections but also all other subgroups and families that share common products. This will enable users to identify relationships and usage patterns of a specific product (ANC) across the entire portfolio.

2. Report Setup
The report page contains a few key elements:
Slicer 1: A single-selection slicer for sch_dim_family_global[sub_group].
- (Filtered by nothing)
Slicer 2: A multi-selection slicer for sch_dim_family_global[family].
-(Filtered by sub_group slicer only to show only relevant families)
Slicer 3: Multi-selection slicer for sch_dim_plant_global[plant_code]
-(Filtered by sub_group and family slicers to show only relevant plants)
Slicer 4: Multi-selection slicer for sch_dim_anc_global[anc_code_old]
-(Filtered by sub_group and family slicers to show only relevant ancs/products)

Model view:

If I make the sub_group interact with the table, it will show only those rows for the specific selected sub_group and family...
Goal:

If I disable interaction between sub-group and family slicers with the table and select individually the shared ANCs it works partially - above is the wanted output, show all subgroups and families that share the same ANC as the selected values... but it needs to be functional - since if I click "select all" on filtered ANC slicer it will show all ANCs completely overriding the relationship with the ancs relevant to those specific families going beyond the only two available values for that subgroup and family... (only works if I select individually, but sometimes the qty of selections might be overwhelming) 

3. Detailed Interaction Logic
This table must have its direct interactions with the sub_group and family slicers turned off.
Its filtering behavior must be controlled by a custom DAX measure that follows this specific logic:
Identify ANCs from Selection: The measure must first identify the complete list of unique anc_code_old values that belong to the sub_group and family chosen in the slicers.
Display All Related Data: The table must then display data for all sub-groups and families in the entire dataset that are associated with any of the anc_code_old values identified in the previous step.

4. Model and Filters
·       Quantitative Data to calculate SUM(sch_fact_material_availability_global[quantity])
·       sch_dim_family_global[sub_group] (used in table)
·       sch_dim_family_global[family] (used in table)

·       sch_dim_plant_global[plant_code] (used in table)
·       sch_dim_anc_global[anc_code_old] (used in table)

I already tried treatas, calculated table, userelationship, create a flag, etc, but I'm struggling with it... So I basically need help with this - I hope it is clear, if you read this far - thanks a lot and thanks in advance for your help!

[johnt75 · Accepted solution]

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]
        )
    )
)

[johnt75 · Accepted solution]

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]
        )
    )
)

[Vinicius_Buba]

Hello @johnt75 - on your suggestion, the behavior is similar to others/old approach:
If I select individual ANCs, it works... If I "select all" available ANCs, it breaks the visual and bypasses override data...
Slicers are set to this disconnect table (in my case, I called it ANC_Relationship,) and it is working partially. Maybe it is something else?

Thx for your time!

[johnt75]

Are you certain that the 2 ANCs you selected actually have values for the months in question? 

I partially understand the reason for the different behaviour between when you choose the 2 options individually and when you use the select all feature. Using select all means that no filters are generated from that slicer, rather than using the individual values as filters as happens when you select the items.

Do you have interactions turned off between the other slicers and the table visual ? I would have expected the selections in the other slicers to affect the list of ANCs returned by the VALUES clause in the TREATAS.

[Vinicius_Buba]

It has values for those months, but even if it were zero, it should work hierarchically correctly. The interactions of slicers and visuals on the screenshot I shared with you are disabled by default, as all slicers are now coming from a disconnected table. The treatas clause function works only if selecting individual ANCs. The fields on the table are natural dimension fields from the respective dimensions

[johnt75 · Accepted solution]

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.

[Vinicius_Buba]

That was exactly the issue - values wasn't working properly even though there wasn't explict relationship. I reactivated the interaction between the disconnected table and the data table, and it worked! Thanks a lot mate! 🙂

[v-hjannapu]

Hi @Vinicius_Buba,

I would also take a moment to thank @Ahmed-Elfeel  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Regards,
Harshitha.

[Vinicius_Buba]

Hello @v-hjannapu, thanks for the comment - below my response 🙂

[Ahmed-Elfeel]

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)

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.

[Vinicius_Buba]

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! 

[Ahmed-Elfeel]

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.