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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sai_Alkesh
Helper II
Helper II

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Hello @Pragati11 

 

Thank you so much. This now works.🙂

 

 

Regards

sai.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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