Forum Discussion

ManaT's avatar
ManaT
Frequent Visitor
1 year ago
Solved

Dim Customer when used to filter gives wrong values

I have a dashboard with multiple dims and facts. I am trying to use Dim Customer [Customer] field to filter the FactMargin table. There is a relationship 1-* between the two. Now when I try to use Gr...
Need Help
Show and Tell
  • HarishKM's avatar
    HarishKM
    1 year ago

    ManaT  Hey,
    I will below steps to troubleshoot my issue.

    your issue is related to the filtering context in Power BI and how the relationships between tables are affecting calculations:

    • Ensure that the 1-* relationship between Dim Customer and FactMargin is correctly setup and active. The filtering should propagate correctly across this relationship.
    • The measure you used:


    I will use this below dax

    CM =

    VAR CustIDs = VALUES( 'Dim Customer'[Customer] )

    RETURN

    CALCULATE(

    [Contribution Margin],

    REMOVEFILTERS('Dim Customer'),

    TREATAS(CustIDs, 'FactMargin'[customer] ))

     

    • I will reassess native measures used for Gross and Contribution Margin without REMOVEFILTERS and TREATAS to understand how context is being applied incorrectly.
    • I will also confirm that slicers are setting the context as expected without additional or hidden filter effects influencing results.

    Thanks

    Harish M
    Kindly give Kudos and accept it as solution if its solves your problem

MohamedFowzan1's avatar
MohamedFowzan1
Super User
1 year ago

Hi ManaT 

 

This definitely seems to be an issue with how the join is happening.

 

Ensure the keys in Dim Customer and FactMargin match perfectly, with no blanks or mismatches. (Currently inactive or ambiguous relationship should be the issue)

 

This measure with TREATAS works perfectly because it explicitly binds the selected customers from Dim Customer to the customer keys in FactMargin and removes other filters, ensuring the filter context aligns. The default measure may get lost in ambiguous relationships

 

Make sure the keys in dim customer exist in fact margin

 

Consider reviweinf the model:

- Especially the match/mismatch between the key values should give you the reason for this discrepancy

- Check on the possibility to have the column for filtering directly within the Fact table