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

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
UHS
Helper I
Helper I

Need help with relationship between two tables

Hi All,
I am having two tables between which there is no relation plus there is no unique key to identify unique records.
COls from table A to be used

Year-QuarterCost CentreOP_expense_Sub_catAmt_InQ_000s_Actual1Actual Allocation1Amt_InQ_000s_Budget1Budget Allocation1

COls from table B

FC_LOBCost CentreFunction codeAct SKF%

Final Expected result from both tables

Year-QuarterFC_LOBCost CentreFunction codeOP_expense_catOP_expense_Sub_catAmt_InQ_000s_Actual1Act SKF%Actual Allocation1Amt_InQ_000s_Budget1

Budget Allocation1

The cost centre in both the tables are not unique and for one costcentre there are multiple function codes. i want this func code col and act skf% to be in the table A
Both table have many to many relationship created and i have even added the cols from both the table in table visual. i am using related func and lokup func to add the table b col in table A but i get errorrs for multiple values are there where single value is expected.
How can i achieve this..
Appretiate quick help

5 REPLIES 5
ryan_mayu
Super User
Super User

@UHS 

could you pls provide some sample data and expected output?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hello Ryan,
thanks for your message. 
PFB data for reference

Table ATable BTable BTable BTable ATable ATable ATable B
Year-QuarterLBCCFCCatSubcatAmountAct%
2024-Q1NonCore1000000000-AA000CompensationBonus7.28499.6
2024-Q1NonCore1000000000-AA000CompensationOther Benefits13.705499.6
2024-Q1NonCore1000000000-AA000CompensationPension Expense5.7077699.6

2024-Q1

NonCore1000001111-AA1111Invest ExpInvest Exp-31.391260.4
2024-Q1NonCore1000001111-AA1111CompensationBonus7.2840.4
2024-Q1NonCore1000001111-AA1111CompensationOther Benefits13.70540.4
2024-Q1NonCore1000001111-AA1111CompensationPension Expense5.707760.4

 

I have highlighted the columns to identify which is coming from table A and B. Many to many relation is created between these two tables and this is what i see in my report visual right now.
Now i want to create two logics, i.e. if the cat is Invest Exp then Act% should be 100 else as it is and Fc should be AA1010 else as it is. so expected result should be as below :

How can i achieve it.
Note: @ different tables.. Joined based on mamuy to many relation

Table ATable BTable BTable BTable ATable ATable ATable B
Year-QuarterLBCCFCCatSubcatAmountAct%
2024-Q1NonCore1000000000-AA000CompensationBonus7.28499.6
2024-Q1NonCore1000000000-AA000CompensationOther Benefits13.705499.6
2024-Q1NonCore1000000000-AA000CompensationPension Expense5.7077699.6
2024-Q1NonCore1000001111-AA1010Invest ExpInvest Exp-31.39126100
2024-Q1NonCore1000001111-AA1111CompensationBonus7.2840.4
2024-Q1NonCore1000001111-AA1111CompensationOther Benefits13.70540.4
2024-Q1NonCore1000001111-AA1111CompensationPension Expense5.70776

0.4

Pls provide the sample data for table A and B separately.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




d_m_LNK
Resolver I
Resolver I

How are you trying to add the column?  Within a measure on the visual or with a calcualted column on the table?  The error you are getting is usually a context error where the function you wrote is expecting a single value (row context) but a column of multiple values is referenced instead of a single value.  

 

Could you paste the code of your measure/calculated column for us to better understand what's going on?

Hello,
PFB data for reference

Table ATable BTable BTable BTable ATable ATable ATable B
Year-QuarterLBCCFCCatSubcatAmountAct%
2024-Q1NonCore1000000000-AA000CompensationBonus7.28499.6
2024-Q1NonCore1000000000-AA000CompensationOther Benefits13.705499.6
2024-Q1NonCore1000000000-AA000CompensationPension Expense5.7077699.6

2024-Q1

NonCore1000001111-AA1111Invest ExpInvest Exp-31.391260.4
2024-Q1NonCore1000001111-AA1111CompensationBonus7.2840.4
2024-Q1NonCore1000001111-AA1111CompensationOther Benefits13.70540.4
2024-Q1NonCore1000001111-AA1111CompensationPension Expense5.707760.4

 

I have highlighted the columns to identify which is coming from table A and B. Many to many relation is created between these two tables and this is what i see in my report visual right now.
Now i want to create two logics, i.e. if the cat is Invest Exp then Act% should be 100 else as it is and Fc should be AA1010 else as it is. so expected result should be as below :

How can i achieve it.
Note: @ different tables.. Joined based on mamuy to many relation

Table ATable BTable BTable BTable ATable ATable ATable B
Year-QuarterLBCCFCCatSubcatAmountAct%
2024-Q1NonCore1000000000-AA000CompensationBonus7.28499.6
2024-Q1NonCore1000000000-AA000CompensationOther Benefits13.705499.6
2024-Q1NonCore1000000000-AA000CompensationPension Expense5.7077699.6
2024-Q1NonCore1000001111-AA1010Invest ExpInvest Exp-31.39126100
2024-Q1NonCore1000001111-AA1111CompensationBonus7.2840.4
2024-Q1NonCore1000001111-AA1111CompensationOther Benefits13.70540.4
2024-Q1NonCore1000001111-AA1111CompensationPension Expense5.70776

0.4

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

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

"); $(".slidesjs-pagination" ).prependTo(".pagination_sec"); $(".slidesjs-pagination" ).append("
"); $(".slidesjs-play.slidesjs-navigation").appendTo(".playpause_sec"); $(".slidesjs-stop.slidesjs-navigation").appendTo(".playpause_sec"); $(".slidesjs-pagination" ).append(""); $(".slidesjs-pagination" ).append(""); } catch(e){ } /* End: This code is added by iTalent as part of iTrack COMPL-455 */ $(".slidesjs-previous.slidesjs-navigation").attr('tabindex', '0'); $(".slidesjs-next.slidesjs-navigation").attr('tabindex', '0'); /* start: This code is added by iTalent as part of iTrack 1859082 */ $('.slidesjs-play.slidesjs-navigation').attr('id','playtitle'); $('.slidesjs-stop.slidesjs-navigation').attr('id','stoptitle'); $('.slidesjs-play.slidesjs-navigation').attr('role','tab'); $('.slidesjs-stop.slidesjs-navigation').attr('role','tab'); $('.slidesjs-play.slidesjs-navigation').attr('aria-describedby','tip1'); $('.slidesjs-stop.slidesjs-navigation').attr('aria-describedby','tip2'); /* End: This code is added by iTalent as part of iTrack 1859082 */ }); $(document).ready(function() { if($("#slides .item").length < 2 ) { /* Fixing Single Slide click issue (commented following code)*/ // $(".item").css("left","0px"); $(".item.slidesjs-slide").attr('style', 'left:0px !important'); $(".slidesjs-stop.slidesjs-navigation").trigger('click'); $(".slidesjs-previous").css("display", "none"); $(".slidesjs-next").css("display", "none"); } var items_length = $(".item.slidesjs-slide").length; $(".slidesjs-pagination-item > button").attr("aria-setsize",items_length); $(".slidesjs-next, .slidesjs-pagination-item button").attr("tabindex","-1"); $(".slidesjs-pagination-item button").attr("role", "tab"); $(".slidesjs-previous").attr("tabindex","-1"); $(".slidesjs-next").attr("aria-hidden","true"); $(".slidesjs-previous").attr("aria-hidden","true"); $(".slidesjs-next").attr("aria-label","Next"); $(".slidesjs-previous").attr("aria-label","Previous"); //$(".slidesjs-stop.slidesjs-navigation").attr("role","button"); //$(".slidesjs-play.slidesjs-navigation").attr("role","button"); $(".slidesjs-pagination").attr("role","tablist").attr("aria-busy","true"); $("li.slidesjs-pagination-item").attr("role","list"); $(".item.slidesjs-slide").attr("tabindex","-1"); $(".item.slidesjs-slide").attr("aria-label","item"); /*$(".slidesjs-stop.slidesjs-navigation").on('click', function() { var itemNumber = parseInt($('.slidesjs-pagination-item > a.active').attr('data-slidesjs-item')); $($('.item.slidesjs-slide')[itemNumber]).find('.c-call-to-action').attr('tabindex', '0'); });*/ $(".slidesjs-stop.slidesjs-navigation, .slidesjs-pagination-item > button").on('click keydown', function() { $.each($('.item.slidesjs-slide'),function(i,el){ $(el).find('.c-call-to-action').attr('tabindex', '-1'); }); var itemNumber = parseInt($('.slidesjs-pagination-item > button.active').attr('data-slidesjs-item')); $($('.item.slidesjs-slide')[itemNumber]).find('.c-call-to-action').attr('tabindex', '0'); }); $(".slidesjs-play.slidesjs-navigation").on('click', function() { $.each($('.item.slidesjs-slide'),function(i,el){ $(el).find('.c-call-to-action').attr('tabindex', '-1'); }); }); $(".slidesjs-pagination-item button").keyup(function(e){ var keyCode = e.keyCode || e.which; if (keyCode == 9) { e.preventDefault(); $(".slidesjs-stop.slidesjs-navigation").trigger('click').blur(); $("button.active").focus(); } }); $(".slidesjs-play").on("click",function (event) { if (event.handleObj.type === "click") { $(".slidesjs-stop").focus(); } else if(event.handleObj.type === "keydown"){ if (event.which === 13 && $(event.target).hasClass("slidesjs-play")) { $(".slidesjs-stop").focus(); } } }); $(".slidesjs-stop").on("click",function (event) { if (event.handleObj.type === "click") { $(".slidesjs-play").focus(); } else if(event.handleObj.type === "keydown"){ if (event.which === 13 && $(event.target).hasClass("slidesjs-stop")) { $(".slidesjs-play").focus(); } } }); $(".slidesjs-pagination-item").keydown(function(e){ switch (e.which){ case 37: //left arrow key $(".slidesjs-previous.slidesjs-navigation").trigger('click'); e.preventDefault(); break; case 39: //right arrow key $(".slidesjs-next.slidesjs-navigation").trigger('click'); e.preventDefault(); break; default: return; } $(".slidesjs-pagination-item button.active").focus(); }); }); // Start This code is added by iTalent as part of iTrack 1859082 $(document).ready(function(){ $("#tip1").attr("aria-hidden","true").addClass("hidden"); $("#tip2").attr("aria-hidden","true").addClass("hidden"); $(".slidesjs-stop.slidesjs-navigation, .slidesjs-play.slidesjs-navigation").attr('title', ''); $("a#playtitle").focus(function(){ $("#tip1").attr("aria-hidden","false").removeClass("hidden"); }); $("a#playtitle").mouseover(function(){ $("#tip1").attr("aria-hidden","false").removeClass("hidden"); }); $("a#playtitle").blur(function(){ $("#tip1").attr("aria-hidden","true").addClass("hidden"); }); $("a#playtitle").mouseleave(function(){ $("#tip1").attr("aria-hidden","true").addClass("hidden"); }); $("a#play").keydown(function(ev){ if (ev.which ==27) { $("#tip1").attr("aria-hidden","true").addClass("hidden"); ev.preventDefault(); return false; } }); $("a#stoptitle").focus(function(){ $("#tip2").attr("aria-hidden","false").removeClass("hidden"); }); $("a#stoptitle").mouseover(function(){ $("#tip2").attr("aria-hidden","false").removeClass("hidden"); }); $("a#stoptitle").blur(function(){ $("#tip2").attr("aria-hidden","true").addClass("hidden"); }); $("a#stoptitle").mouseleave(function(){ $("#tip2").attr("aria-hidden","true").addClass("hidden"); }); $("a#stoptitle").keydown(function(ev){ if (ev.which ==27) { $("#tip2").attr("aria-hidden","true").addClass("hidden"); ev.preventDefault(); return false; } }); }); // End This code is added by iTalent as part of iTrack 1859082
Top Solution Authors
Top Kudoed Authors