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 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!!!