Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
gbarr12345
Post Partisan
Post Partisan

Relationship query

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.

 

gbarr12345_0-1717628337614.png

 

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

 

gbarr12345_3-1717628466390.png

 

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

 

gbarr12345_1-1717628423534.png

 

gbarr12345_2-1717628438652.png

 

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.

 

gbarr12345_4-1717628636841.png

 

 

Any idea on how to make these blank?

 

If you need any more info please let me know.

 

Many Thanks in advance.

 

 

 

1 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

vjtianmsft_0-1718098963187.pngvjtianmsft_1-1718098971532.png

The tables where I think there are data problems are in these tables

The filtering relationship between them is as follows:

vjtianmsft_2-1718098987775.png

vjtianmsft_3-1718098996646.png

vjtianmsft_4-1718099015364.pngvjtianmsft_5-1718099023035.png

vjtianmsft_6-1718099037241.png

vjtianmsft_7-1718099044224.png

vjtianmsft_8-1718099059344.png

 

vjtianmsft_9-1718099069868.png

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.

vjtianmsft_10-1718099090854.png

So I think there is something wrong with your original data, you can replace the original sum of Amount by creating  measure

like this :

vjtianmsft_11-1718099122928.png

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.



View solution in original post

6 REPLIES 6
v-jtian-msft
Community Support
Community Support

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.

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.

 

gbarr12345_0-1717645911981.png

 

gbarr12345_1-1717645921613.png

 

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!

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.

 

gbarr12345_0-1717964871971.png

 

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

vjtianmsft_0-1718098963187.pngvjtianmsft_1-1718098971532.png

The tables where I think there are data problems are in these tables

The filtering relationship between them is as follows:

vjtianmsft_2-1718098987775.png

vjtianmsft_3-1718098996646.png

vjtianmsft_4-1718099015364.pngvjtianmsft_5-1718099023035.png

vjtianmsft_6-1718099037241.png

vjtianmsft_7-1718099044224.png

vjtianmsft_8-1718099059344.png

 

vjtianmsft_9-1718099069868.png

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.

vjtianmsft_10-1718099090854.png

So I think there is something wrong with your original data, you can replace the original sum of Amount by creating  measure

like this :

vjtianmsft_11-1718099122928.png

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

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors