Forum Discussion
How to Aggregate Values from a Dimension Table in a Donut Chart with Filter Context
Hi everyone,
I'm facing a modeling issue I really need your help.
My Data Model:
fact_table: Contains transactions with columns like Dealer, AREA_NAME, Customer, Date, ITEM_NO, etc.
Product_table (dimension): Contains Item_parts_no,Item_parts_desc and Fleet_Quantity.
Dealers_table (dimension): Contains details about dealers.
Region_table (dimension): Contains region information.
Date_table (dimension): Standard calendar table.
The cardinality between the 'fact_table' and the dimensions table are Many to One (*:1) and the Cross-filter direction are 'Single'.
Please see the screenshot below:
Filter context:
I have few slicers set with the dimensions_table in order to filter the datas. The charts have to be dynamic (change with the filter context).
What I want to do:
I want to build a donut chart that shows the sum of product quantities per dealer and per region.
However, the Fleet_Quantity field is in the Product_table, not in the fact table.
Also, the visual must respond to several slicers (filters) including:
Machine type (Product Type)
Dealer
Year
AREA_NAME
The problem:
Because Quantity is in the dimension table (Product_table), it doesn't naturally respond to filters coming from the fact_table. So, my donut chart doesn't reflect the correct filtered totals.
What I've tried:
RELATED() to bring Fleet_Quantity into the fact table (but the relationship is not direct).
For example:
Regional_Fleet_Qty =CALCULATE( SUMX(fact_table, RELATED (Product_table[Fleet_quantity])),
REMOVEFILTERS(Dealers_table[Dealer]))
TREATAS() to create a virtual relationship in a measure.
- For example:
Regional_Fleet_Qty =
VAR SelectedDealers = VALUES(Dealers_table[Dealer])
VAR SelectedRegions =VALUES(Region_table[AREA_NAME])
RETURN
CALCULATE(SUMX(fact_table, RELATED (Product_table[Fleet_quantity])),
TREATAS(SelectedDealers, fact_table[Dealer]),
TREATAS(SelectedRegions, fact_table[AREA_NAME]))
- For example:
But it's not working
My question:
How can I properly create a DAX measure or model my data so that I can:
Aggregate Fleet_Quantity from the Product_table
Slice/filter correctly using fields from the fact_table (AREA_NAME, Dealer, Year, etc.)
Display the results correctly in a donut chart grouped by AREA_NAME or Dealer
Any advice on how to restructure the model or write the correct DAX would be greatly appreciated !
Thanks in advance !
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.
6 Replies
johnt75Super User
You could use the fact table as a filter. This will expand to the product table, including any filters which are placed on the fact table, so only the relevant products will be included.
Regional_Fleet_Qty = CALCULATE ( SUM ( Product_table[Fleet_quantity] ), fact_table )Hi johnt75,
Thanks a lot for your reply and for suggesting a way to apply the fact table as a filter context in CALCULATE.
While this approach didn’t fully resolve my issue in this particular case, it reminded me how powerful context transition can be when used wisely and I’ll definitely keep this idea in mind for other modeling challenges.
I truly appreciate your help and the time you spent looking into this!
Best regards,
Tom_Herbet
- v-karpurapud
Community Support
Hi 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.
