cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Super User II
Super User II

Re: Data Relationships

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





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
Highlighted
Super User II
Super User II

Re: Data Relationships

Hi @Sai_Alkesh ,

 

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

 

Thanks,

Pragati





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


Proud to be a Super User!




Highlighted
Helper I
Helper I

Re: Data Relationships

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.

Highlighted
Super User II
Super User II

Re: Data Relationships

Hi @Sai_Alkesh ,

 

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

 

Thanks,

Pragati





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


Proud to be a Super User!




Highlighted
Helper I
Helper I

Re: Data Relationships

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

Highlighted
Super User II
Super User II

Re: Data Relationships

Hi @Sai_Alkesh ,

 

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

 

Thanks,

Pragati





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


Proud to be a Super User!




Highlighted
Helper I
Helper I

Re: Data Relationships

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

Highlighted
Super User II
Super User II

Re: Data Relationships

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





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


Proud to be a Super User!




Highlighted
Helper I
Helper I

Re: Data Relationships

Hello @Pragati11 

 

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

FIT table for Currency, Cost element

 

Regards

Sai

Highlighted
Super User II
Super User II

Re: Data Relationships

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





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


Proud to be a Super User!




Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

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

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors