Forum Discussion
Relationship query
Hi,gbarr12345
Thank you for your replay.According to the test data you provided in your second reply, I found the possible problem:It may still be due to the wrong data in the data source, which leads to the final Amount will show the results
Here is my test (check the process: you can also try similar steps to find out)
Here is the test file you provided
The tables where I think there are data problems are in these tables
The filtering relationship between them is as follows:
When Customer ID = 27, if in accordance with the requirements of the Tas Data table at this time in the field Tasman Only or BC & Tas = “TO”, then the Amount should be filtered, the three lines of data are blank, in this case, sum(Amount) The result of the calculation is blank, but in fact, according to the data you can provide to see, finally filtered out the three rows of data is not empty (100,48,350) so the final in the visual presentation of the results of the calculation sum of amount = 100 + 48 + 350 = 498
like this.
So I think there is something wrong with your original data, you can replace the original sum of Amount by creating measure
like this :M_SUM_Amount = IF(SELECTEDVALUE('Tas Data'[Tasman Only or BC & Tas])="TO", BLANK(), CALCULATE( SUM('Sales Table'[Amount]),FILTER(ALL('Sales Table'),'Sales Table'[Customer ID]=MAX('Sales Table'[Customer ID] ))) )Looking forward to your replay.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,gbarr12345
Thank you for your reply.
Regarding the unexpected display of your data when it should be empty
I would like to add some information, first of all according to the information you provided at the beginning
The value of AMOUNT is displayed with multiple filters, i.e. [TASMAN CUSTOMER NO.]
[DESCRIPTION] and [Tasman only or BC & Tasman] as well as other filters (if an external slicer exists) (the screenshot you provided shows amount=117).
What I mean by this is that when there are more filters and more relationships, the result is more likely to be null, rather than showing result=117 as it is now.
So I think it is very likely that there is something wrong with the data itself, (the probability that there is something wrong with the relationship between the tables is very small, because the more complicated the relationship is, the more likely it is that nulls will be filtered out).
So please check your data itself if for [Tasman only or BC & Tasman] = “TO”, there is a corresponding value for amount.
amount has a corresponding value, not null as you expect.
If you check the data source and find that for BC ONLY data, the corresponding amount is blank, then you should re-check the following relationship
Otherwise, I think the data itself should have redundant duplicate values.
Looking forward to your reply.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Carson,
Thank you for the detailed information.
I've checked the data and the customers that are appearing as 117 should not have any number attached to them (should say null or something similar) as they are not BC and Tasman. These numbers are Tasman only which doesn't have any number attached to them as they come from a seperate data source.
I'm trying to see what the best way to make these show as blank or null would be and it's proving difficult as the 117 seems to be linked to 1 customer so it's incorrect.
Hi,gbarr12345
Thank you for your replay.According to the test data you provided in your second reply, I found the possible problem:It may still be due to the wrong data in the data source, which leads to the final Amount will show the results
Here is my test (check the process: you can also try similar steps to find out)
Here is the test file you provided
The tables where I think there are data problems are in these tables
The filtering relationship between them is as follows:
When Customer ID = 27, if in accordance with the requirements of the Tas Data table at this time in the field Tasman Only or BC & Tas = “TO”, then the Amount should be filtered, the three lines of data are blank, in this case, sum(Amount) The result of the calculation is blank, but in fact, according to the data you can provide to see, finally filtered out the three rows of data is not empty (100,48,350) so the final in the visual presentation of the results of the calculation sum of amount = 100 + 48 + 350 = 498
like this.
So I think there is something wrong with your original data, you can replace the original sum of Amount by creating measure
like this :M_SUM_Amount = IF(SELECTEDVALUE('Tas Data'[Tasman Only or BC & Tas])="TO", BLANK(), CALCULATE( SUM('Sales Table'[Amount]),FILTER(ALL('Sales Table'),'Sales Table'[Customer ID]=MAX('Sales Table'[Customer ID] ))) )Looking forward to your replay.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Carson,
Thanks a million for your solution, I can happily say that it's working for me now.
Thanks again!!!