Forum Discussion

Vinicius_Buba's avatar
Vinicius_Buba
New Member
10 months ago
Solved

Relationship/Slicer Issue

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 sh...
  • johnt75's avatar
    10 months ago

    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's avatar
    johnt75
    10 months ago

    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.