Forum Discussion
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 Gross Margin and Contribution margin values from the FactMargin table and filter using a slicer from Dim Customer [Customer]. When I use the filters the margin values become very different (they dont neccessarily follow a pattern) but when I use the native FactMargin Customer field to filter, the value show correct. When i also use this measure, it shows correct:
CM =
VAR CustIDs = VALUES( 'Dim Customer'[Customer] )
RETURN
CALCULATE(
[Contribution Margin],REMOVEFILTERS( 'Dim Customer' ),TREATAS( CustIDs, 'FactMargin'[customer] ))
I am unable to figure out what is wrong since it also doesnt neccessarily follow a pattern though it does look like it has to do with the DimCustomer to Customer join. The filter does filter out the corrrect customer but the values are wrong. I have attached an image where GM and CM are based on the formula above while long measures are the default ones showing incorrect values.
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 daxCM =
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
9 Replies
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
Hi,
Share the download link of the PBI file. Show the problem and expected result there.
I am unable to provide a pbix as this is a confidential data for a client. Recreating it would be hard as well as it might not capture the whole issue.
DimCustomer[Customer] (dimension table)
FactMargin[Customer] (fact table)
Relationship: 1 → * (Dim → Fact)
Issue: When filtering by DimCustomer[Customer], Gross Margin (GM) & Contribution Margin (CM) give wrong results. But when filtering by FactMargin[Customer], results are correct.
You already tested a workaround using:
CM =
VAR CustIDs = VALUES( 'Dim Customer'[Customer] )
RETURN
CALCULATE(
[Contribution Margin],
REMOVEFILTERS( 'Dim Customer' ),
TREATAS( CustIDs, 'FactMargin'[Customer] )
)
…and that gives the expected results. ✅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 daxCM =
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 problemHi ManaT ,
Thank you for reaching out to the Microsoft Community Forum.
Hi MohamedFowzan1 , Ashish_Mathur , Shahid12523 , HarishKM , Thank you for your prompt responses.
Hi ManaT , Could you please try the proposed solutions shared by Shahid12523 and HarishKM . Please do let us know if you have any further queries.
Regards,
Dinesh
Hi ManaT ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh
Hi @ManaT ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh
