How to Aggregate Values from a Dimension Table in a Donut Chart with Filter Context

[Tom_Herbet]

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

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 !

[v-karpurapud]

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.

[Tom_Herbet]

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 · Accepted solution]

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.

[Tom_Herbet]

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!

[johnt75]

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 )

[Tom_Herbet]

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