Forum Discussion
Relationship/Slicer Issue
- 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] ) ) ) - 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.
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_Buba10 months agoNew Member
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!
- johnt7510 months agoSuper User
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_Buba10 months agoNew Member
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