cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sai_Alkesh
Helper I
Helper I

Data Relationships

Hello,

I have the following tables:

1. CC_Master (Includes unique cost center records columns - Cost_Center (numeric), and TM1_CC_Lookup (AlphaNum)

2. FIT table (transaction table), which includes Cost_center column and mapped to CC_Master on many to one. Cost_center column of FIT table connects to TM1_CC_Lookup column in CC_master & direction is single 

3. SAP table (transaction table), which includes Cost_center column mapped to CC_Master on many to one, single direction. Cost Center column of SAP connects to Cost_Center column of CC_Master.

 

On my report, i have one matrix table getting data from FIT table, which includes Date, Cost center, currency, cost element, country etc., & on another table, i have SAP data.

 

When i apply filters from other organization values (country, Cost center, currency etc.,), and select cost element from FIT table, the corresponding SAP table filters data only to Cost Center, but not at the level of Cost element

 

Pasted below is my data

 

Sai_Alkesh_0-1594717450258.png

 

Appreciate if anyone could help me please.

 

Regards

Sai.

 

1 ACCEPTED SOLUTION

Hi @Sai_Alkesh ,

 

I have shared the solution for your issue with the pbix file as a private message.

Please check if that works.

 

Thanks,

Pragati


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




View solution in original post

11 REPLIES 11
Pragati11
Super User
Super User

Hi @Sai_Alkesh ,

 

Try making your relationships bi-directional and see if it works.

 

Thanks,

Pragati


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




Hello @Pragati11 

 

Thanks for the quick reply.

 

If i change the cardinality to bi-directional, then the values in FIT table are all wrong, as it sums up and it still doesnt filter the SAP table.

 

Regards

Sai.

Hi @Sai_Alkesh ,

 

Is there a way you can create a relationship between FIT and SAP table?

 

Thanks,

Pragati


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




Hello @Pragati11 

 

I can establish relationship between FIT & SAP and tried that too, however filtering issue still persists & to arrest circular referencing, i need to de-activate one of the active links between FIT & CC_Master & SAP

 

Regards

Sai

Hi @Sai_Alkesh ,

 

Yes you will have to de-activate the relationship between CC-Master and FIT.

 

Thanks,

Pragati


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




Hello @Pragati11 

 

I did that too. Below is the modified relationship table. After modifying, SAP table still doesnt filter based on value selected in FIT table

 

Sai_Alkesh_0-1594721708407.png

 

 

Regards

Sai

Hi @Sai_Alkesh ,

 

A quick question on the following statement by you:

When i apply filters from other organization values (country, Cost center, currency etc.,), and select cost element from FIT table, the corresponding SAP table filters data only to Cost Center, but not at the level of Cost element

 

  1. From which table the highlighted columns are used for filtering? (RED highlighted)
  2. Can you share some screesnhots of visualisations and slicers/filters you are using in Power BI?
  3. Is there Cost Element column in SAP table?

Thanks,

Pragati


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




Hello @Pragati11 

 

The filters are applied from CC_Master (for Cost Center, Country) & from

FIT table for Currency, Cost element

 

Regards

Sai

Hi @Sai_Alkesh ,

 

I still don't see any screeshots in your reply.

Please make relationship between CC_Master and SAP bi-directional, as you are trying to filter data in SAP table using filters from CC_Master.

 

If still doesn't work, try sharing your pbix file.

 

Thanks,

Pragati


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




Hi @Sai_Alkesh ,

 

I have shared the solution for your issue with the pbix file as a private message.

Please check if that works.

 

Thanks,

Pragati


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




View solution in original post

Hello @Pragati11 

 

Thank you so much. This now works.🙂

 

 

Regards

sai.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!