Relationship query

[gbarr12345]

Hi everyone,

I have a query I hope you'd be able to help with.

I have data that I've had to split between Business Central Only and Tasmanian data that's in Business Central and I created two new tables that I've added into a PB file that has already existing tables including the Sales table which has the transactional data.

The BC only data has its own customer code and the BC and Tasman data have different customer codes.

I've the relationships set and they seem to work correctly.

Here are the relationships I created below with the new tables:

When I pull the Tasman Customer number from the Tasman Data table and the Customer description from the Customer table and the Amount from the sales table, the BC and Tasman customer data is correct but for the BC ONLY data it should be blank but it's pulling in data for only one customer (Arbour and 117) for all the Tasman Only Customers when they should be blank as they have no data that's in the customer or sales table as they came from a seperate file.

Any idea on how to make these blank?

If you need any more info please let me know.

Many Thanks in advance.

[v-jtian-msft]

Hi,@gbarr12345 
I am glad to help you.

According to your description, some of the data in the visual rendering is showing data unexpectedly (it should have shown as empty).

This suggests that there may be a problem with the relationship between the tables or the direction of the filtering, resulting in unexpected results.

Given that your data model has some complexity, here are some suggestions that you can refer to troubleshoot the problem and resolve it as best you can.

Please make a copy of your pbix file first to avoid any accidental modification of the table data.

1. Please check whether the relationship between tables is set correctly, including “Cardinality” and “Cross filter direction”.

Here is the documentation
URL:Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn
The direction of screening is very important, so please check that you are screening in the right direction.
2.Create measures for Conditional Data Display: If you want to conditionally display data based on whether a Tasmanian customer has corresponding BC ONLY data, consider creating a DAX measure that checks for the presence of data in both the BC ONLY and Tasmanian tables.
like this:

M_result =if(
    isblank(related(‘Tasman Data’[Tasman Customer Number])),
    blank(),
    sum(‘Sales’[Amount])
)

Determine if it's really what you expected by creating a measure: "for the BC ONLY data it should be blank but it's pulling in data for only one customer (Arbour and 117) for all the Tasman Only Customers when they should be blank as they have no data that's in the customer or sales table as they came from a seperate file".

3.Check that your data is correct
Ensure that there are no unexpected data entries in your BC ONLY and Tasmanian tables that might be causing the issue. Sometimes, data anomalies or unexpected duplicates can lead to issues in how data is displayed in Power BI reports.

If the problem persists after trying the above troubleshooting, you can try creating a new measure/calculate column without modifying any model structure or relationships.

You can try to create a new measure/calculate column without modifying any model structure or relationship.

to artificially control the display of results (in the absence of a better solution).
like this:

calculate column_=
if(‘Table’[description]=’Arbour’,
blank(),
‘Tasman Data’[Tasman only or BC & Tasman]
)

Could you please provide more non-sensitive information about the key tables and corresponding fields (as many of the tables in the information you provided do not show the full field information, for example, I am not sure what the real table name is for the 'Table'[description]=' Arbour' ,what is the real table name of this Table)

If you could provide more information about the fields and relationships associated with the table, preferably a pbix file that does not contain sensitive information, it would be very helpful in solving your problem.

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.

[gbarr12345]

Hi Carson,

Thank you for your response.

Yes, you are correct in saying that the data is showing unexpectedly when it should be empty.

I'm looking at the relationships and it's hard to see anything wrong with them. I could be wrong though.

The data is correct as it's data that was already there so no alterations have been made.

I've attached the link to sample data. I've tried recreating the issue but it's proving difficult.

https://drive.google.com/file/d/1s-UJvL3YiLv1vqOwqmbjvHzX6fkNThFW/view?usp=drive_link

If there's something that you see then any help would be massively appreciated!

[v-jtian-msft]

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.

[gbarr12345]

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.

[v-jtian-msft · Accepted solution]

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.

[gbarr12345]

Hi Carson,

Thanks a million for your solution, I can happily say that it's working for me now.

Thanks again!!!