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
KRISH80
Helper II
Helper II

DAX to get Multiple Values for a record from another table

Dear All,

 

I have a table called 'Record ID' which has distinct list of record ids and then i have a 'Details' table which has various details related to that record id. One record ID might have multiple values in another table. Example as below - 

Table 1 : 

Record IDOpportunity NameAccount NameOpportunity Owner
0064G000013rD6mQAEAbhyudaya Co-Operative Bank-IT/PO-16/2017-18-40085308-10-$69Abhyudaya Co-operative Bank LtdVinod Iyer
0064G000013snC6QAIAbhyudaya Bank - Nexus Switches and FW CaseAbhyudaya Co-operative Bank LtdAmey Khochare
0064G000013tHYtQAMCisco-Accenture-GGN--Sheetal(DCCOR & others)Accenture Services India Pvt. Ltd.Seshasai Vaddadi
0064G000013rmYiQAIAdobe_Bangalore PTC ExpansionAdobeRaghvendra Singh
0064G0000136tlJQAQRemote officeAirbusMudit Shinghal
0064G0000136tlJQAQRemote officeAirbusMudit Shinghal
0064G000013qkE3QAIRiverbed applianceAirbusMudit Shinghal
0064G000012C2OJQA0uDNS AMC RenewalAirtelKomal Grover
0064G000014qiEkQAIASR 5G expansionAirtelKomal Grover
0064G000014rAlSQAUuDNS AMC-2AirtelKomal Grover

 

Table 2 

Record IDVerticalRegion-NEWBUSUOriginal Close Date
0064G000013rD6mQAEENTWestDISS4/26/2020
0064G000013snC6QAIENTWestDIProduct4/24/2020
0064G000013snC6QAIENTWestSECProduct4/24/2020
0064G000013tHYtQAMITESSouth-1DITraining4/30/2020
0064G000013rmYiQAIITESNorthDIProduct4/27/2020
0064G0000136tlJQAQGlobalsSouth-1DIProduct4/16/2020
0064G0000136tlJQAQGlobalsSouth-1DISS4/16/2020
0064G000013qkE3QAIGlobalsSouth-1DIProduct4/30/2020
0064G000012C2OJQA0M&CNorthSECSS4/17/2020
0064G000014qiEkQAIM&CNorthDIProduct4/22/2020
0064G000014rAlSQAUM&CNorthSECSS4/22/2020
0064G000013rD6nQAEENTWestDWSS4/30/2020
0064G000013qzr9QAAENTSouth-1DIProduct

4/27/2020

 

 

Now i need to update Table 1 with values from Table 2. And i need to ensure that, were ever we have multiple values for one 'Record ID' they should appear as well. In the above example record id 0064G000013snC6QAI has 2 BUs in table 2 called DI & SEC so in table 1 i should have 2 rows one for DI and another one for SU and rest of the values can remain same. It should look like this :

Record IDOpportunity NameAccount NameOpportunity OwnerVerticalRegion-NEWBUSUOriginal Close Date
0064G000013snC6QAIAbhyudaya Bank - Nexus Switches and FW CaseAbhyudaya Co-operative Bank LtdAmey KhochareENTWestDIProduct4/24/2020
0064G000013snC6QAIAbhyudaya Bank - Nexus Switches and FW CaseAbhyudaya Co-operative Bank LtdAmey KhochareENTWestSECProduct4/24/2020

 

I can't merge or append query as few coulmns in the table have been created using "LOOKUP" function.

 

Regards,

Krishna

1 REPLY 1
V-lianl-msft
Community Support
Community Support

Hi @KRISH80 ,

 

Try to create a many to many relationship and set the filtering direction to single.

filter_direction.PNGtest_records.PNG

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

Top Solution Authors