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

[Anonymous]

Hi Team,

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

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

Below is the expected output:

deal_entity_id	developer_name	deal_instrument_name	total_capital_stack
21644	Developer1	deal_instrument_name1	$182000000
21644	Developer1	deal_instrument_name2	$182000000
21644	Developer1	deal_instrument_name3	$182000000
21644	Developer1	deal_instrument_name4	$182000000
21645	Developer1	deal_instrument_name5	$15000000
21786	Developer1		$10000000
21787	Developer1		$10000000
21788	Developer1		$20000000
21789	Developer1		$15000000
21790	Developer1	deal_instrument_name6	$7500000
22063	Developer1		$15000000
22064	Developer1		$182000000
22463	Developer1	deal_instrument_name7	$15000000
22464	Developer1	deal_instrument_name8	$20000000
22480	Developer2	deal_instrument_name9	$18000000
22531	Developer1	deal_instrument_name10	$10000000
22549	Developer1	deal_instrument_name11	$125000000

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

deal_entity_id	developer_name	deal_instrument_name	total_capital_stack
21644	Developer1	deal_instrument_name1	$182000000
21644	Developer1	deal_instrument_name2	$182000000
21644	Developer1	deal_instrument_name3	$182000000
21644	Developer1	deal_instrument_name4	$182000000
21645	Developer1	deal_instrument_name5	$15000000
21790	Developer1	deal_instrument_name6	$7500000
22463	Developer1	deal_instrument_name7	$15000000
22464	Developer1	deal_instrument_name8	$20000000
22480	Developer2	deal_instrument_name9	$18000000
22531	Developer1	deal_instrument_name10	$10000000
22549	Developer1	deal_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.

[Anonymous]

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]

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