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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ramchoudhary
Frequent Visitor

Get all the values from one side to many side of the relationship with all the values from ONE table

Hi Team,

 

I have three different tables and below is how they are connected.

ramchoudhary_0-1682406319647.png

 

My pbix. file is here https://file.io/HlapG57rELb7

 

Below is the expected output:

deal_entity_iddeveloper_namedeal_instrument_nametotal_capital_stack
21644Developer1deal_instrument_name1$182000000
21644Developer1deal_instrument_name2$182000000
21644Developer1deal_instrument_name3$182000000
21644Developer1deal_instrument_name4$182000000
21645Developer1deal_instrument_name5$15000000
21786Developer1 $10000000
21787Developer1 $10000000
21788Developer1 $20000000
21789Developer1 $15000000
21790Developer1deal_instrument_name6$7500000
22063Developer1 $15000000
22064Developer1 $182000000
22463Developer1deal_instrument_name7$15000000
22464Developer1deal_instrument_name8$20000000
22480Developer2deal_instrument_name9$18000000
22531Developer1deal_instrument_name10$10000000
22549Developer1deal_instrument_name11$125000000

 

Where as I am geting below output in PBI as of now.

deal_entity_iddeveloper_namedeal_instrument_nametotal_capital_stack
21644Developer1deal_instrument_name1$182000000
21644Developer1deal_instrument_name2$182000000
21644Developer1deal_instrument_name3$182000000
21644Developer1deal_instrument_name4$182000000
21645Developer1deal_instrument_name5$15000000
21790Developer1deal_instrument_name6$7500000
22463Developer1deal_instrument_name7$15000000
22464Developer1deal_instrument_name8$20000000
22480Developer2deal_instrument_name9$18000000
22531Developer1deal_instrument_name10$10000000
22549Developer1deal_instrument_name11$125000000

 

The output in the PBI currently is giving us only the matching records but we need ot have all the records where " deal_instrument_name" is blank as well and dispplay the available data from "deal" as shown in my expected output.

 

Also, please note that I have already tried it with "show items with no data" and it does not work as it gives blank values from the deal table for "total_capital_stack" column. Here is the snapshot of what we see when we clickn on "show items with no data" unlike our expected output.

ramchoudhary_0-1682406246690.png

 

2 REPLIES 2
Anonymous
Not applicable

Hi,

I'm just a beginner ,But acc to my knowledge I can suggest u to modify data as there is duplicate data in your in dataset which interrupts the expected Output, after transforming data ,

1) If u want ur dataset to remain same ,then u have to group the data using calcuation groups.

or

2)Clear the data and make sure of distinct values .

then the data can be visualised as expected.

 

Thanks .Hope it might strike u a point of thought.

Rudy_D
Resolver I
Resolver I

Hi there,

 

Regarding your data model, I think it's not possible to get what you want.

I've already had this issue before and I managed to modify the fact table ('Deal' table in your case) through Power Query. The trick is to add the column 'Deal instrument name' in your fact table by duplicating rows if there's more than 1 instrument name for one deal.

Not sure that it's the optimal solution anyway.

 

Regards,

 

Rudy

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.