Forum Discussion
How to Aggregate Values from a Dimension Table in a Donut Chart with Filter Context
Hi Tom_Herbet
Thank you for your thoughtful feedback. I'm glad the explanation around filter context and the use of TREATAS() helped clarify the issue.
To ensure model and visuals are functioning correctly, please verify that the columns used in TREATAS() such as fact_table[ITEM_NO] and Product_table[Item_parts_no] match in both data type and content. Also, confirm that slicers like Year, Dealer, Region, and Machine are based on fields from their respective dimension tables and are properly linked to the fact table.
This ensures filter context flows correctly into the fact table and then into the product table via the virtual relationship. When using the Fleet_Quantity_Filtered measure in visuals like donut charts, make sure the grouping field (e.g., Dealer or AREA_NAME) is part of a table that maintains a relationship with the fact table. For troubleshooting, start with a simple table visual using fields like Dealer, ITEM_NO, and the measure to validate logic and filter behavior. If issues persist, feel free to share more details about your visual configuration. I would be happy to assist .
Regards,
Karpurapu D.
Community SupportHi Tom_Herbet
Welcome to the Microsoft Fabric Community Forum. Also, thank you johnt75 for your helpful reponse.
The issue arises due to the placement of the Fleet_Quantity field within the Product_table, which is a dimension table connected to the fact_table via a single-direction, many-to-one relationship. In this configuration, filters applied through slicers such as Year, Dealer, Region, and Machine affect only the fact_table and do not propagate back to the Product_table. Consequently, aggregations of Fleet_Quantity do not reflect the intended filter context.
To address this, it is recommended to define a DAX measure that establishes a virtual relationship, allowing the filtered product context from the fact_table to influence the Product_table. This can be achieved using the TREATAS() function.
The following DAX measure is suggested:
Fleet_Quantity_Filtered :=
CALCULATE (
SUM ( Product_table[Fleet_Quantity] ),
TREATAS (
VALUES ( fact_table[ITEM_NO] ),
Product_table[Item_parts_no]
)
)
This measure ensures that Fleet_Quantity is aggregated only for the products present in the filtered fact_table, thereby aligning the visual output with the slicer selections and delivering accurate results in the donut chart.
If this response resolves your query, kindly mark it as Accepted Solution to help other community members. A Kudos is also appreciated if you found the response helpful.
Thank you for being part of Fabric Community Forum.
Regards,
Karpurapu D,
Microsoft Fabric Community Support Team.
Hi v-karpurapud,
Thank you so much for your detailed and thoughtful response. I really appreciate the time you took to explain the issue around filter context and how it affects dimension tables like Product_table.
Your explanation of how to use TREATAS to build a virtual relationship was especially helpful in understanding how I might get around the limitations of the current model structure.
Even though I haven’t yet managed to fully resolve the issue on my side, your answer gave me valuable insights and new ideas to explore.
Thanks again for your support!
Best regards,
Tom_Herbet
- v-karpurapud
Hi Tom_Herbet
Thank you for your thoughtful feedback. I'm glad the explanation around filter context and the use of TREATAS() helped clarify the issue.
To ensure model and visuals are functioning correctly, please verify that the columns used in TREATAS() such as fact_table[ITEM_NO] and Product_table[Item_parts_no] match in both data type and content. Also, confirm that slicers like Year, Dealer, Region, and Machine are based on fields from their respective dimension tables and are properly linked to the fact table.
This ensures filter context flows correctly into the fact table and then into the product table via the virtual relationship. When using the Fleet_Quantity_Filtered measure in visuals like donut charts, make sure the grouping field (e.g., Dealer or AREA_NAME) is part of a table that maintains a relationship with the fact table. For troubleshooting, start with a simple table visual using fields like Dealer, ITEM_NO, and the measure to validate logic and filter behavior. If issues persist, feel free to share more details about your visual configuration. I would be happy to assist .
Regards,
Karpurapu D.Thank you so much for your response, v-karpurapud. I’ll mark your answer as the solution, as it helped clarify the issue.
From what I now understand, even though the relationship between my fact table and the 'Product_table' is defined as Many-to-One (*:1), the virtual relationship created using TREATAS() on [ITEM_NO] allows multiple matches for 'Fleet_Quantity', and Power BI may select one arbitrarily (please correct me if I'm wrong). Additionally, as you said in your first reply the filters applied through slicers such as 'Year', 'Dealer', 'Region', and 'Machine' only affect the 'fact_table' and do not propagate to 'the Product_table'.
Given this behavior, I’ve decided to bring 'Fleet_Quantity' directly into the fact table. Since there is no unique key that allows for a clean join between the 'fact_table' and 'Product_table', it seems the star schema model may not be appropriate in this scenario.
Thanks again for your help!