How to get the first available value from 3 related tables with some records not having a relationsh

[goscr80]

Hello Folks,

I hope someone can help me out with my problem.  I have 3 related tables, however there are records that aren't having a relationship because values are only available within an expecific table. i.e. certain values only available in L3 without relationship in other tables because simple there are not records to get related with, in my case tables L1 or L2 or value available only in L2 but nothing to be related in table L1 or viceverse. The described situation is not allowing my Visual (table) to create a column that get the existing value depending where the value is available. unfortunately i can not use related function becuase one of the relationship between L1 and L2 are set one to Many. The logic require is as follow: if there is no value in L1, then get L2, but if L2 value is Empty then L3 value.

In Tablueu i'm using the following formula to make it possible:

Sold to Party = IF ISNULL([L1 Sold to Party])
THEN IF ISNULL([L2 Sold to Party])
    THEN [L3 Sold to Party]
        ELSE [L2 Sold to Party] 
        END
ELSE [L1 Sold to Party]
END

Thankfs for the help.

[goscr80]

Thanks Ibdenlin,

Coalesce function doesnt work when there are values with not relationship.  I am trying to create a column that contains the first available sold to party from Sold to part 1, sold to party 2 or Sold to party 3, each of them comes from a diferent table as below. Issue is for getting the values when are only available in table 3 or table 2. Column is required to be used in a visual type table.

[lbendlin]

COALESCE doesn't care where its parameters come from.  Use an appropriate approach to acquire the values (for example via measures).

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[goscr80]

Thanks for prompt response,

Attaching the sample and expected outcome

Thanks,

https://www.dropbox.com/scl/fi/b23enr0ng8mcj9c3ybaca/ExampleReport.pbix?rlkey=27e414c3acx8dwctj0bd2y... 

Excel file:

https://www.dropbox.com/scl/fi/gxlm0qdmfneinqs7sf44o/Example.xlsx?rlkey=hsdge901twnyamdmxrkshi14x&dl... 

Expected output:

[lbendlin]

Not saying that I cheated, but I used the Performance Analyzer to "borrow" the DAX for the initial table. I also modified your data model a bit.

see attached.

[goscr80]

Hello Ibendlin,

The code works great!! Thanks!!! But i see, that you have created a table for only those 3 columns, now i am wondering how it will work or replicate it in a report with approx 22 Columns (all of them from a combinaction from the different tables) and 5 of those columns will require the same approach that it was suggested. I was expecting a calculated column instead a new table, is it that possible?

BR,

goscr80

[lbendlin]

When you are reporting on things that are not there a calculated column won't help.  You need to materialize the data somehow so you get empty cells (rather than nothing).

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[goscr80]

Thanks Again,

I have reproduce the DB and recreted the report. 

https://www.dropbox.com/scl/fi/1drketrri6bnmmz8xlmay/Examplepbi.pbix?rlkey=3um9qumjbanbx8curb2nqufbq... 

Data: 

https://www.dropbox.com/scl/fi/v60vnmp6ihrd6g9m9yj9n/Example.xlsx?rlkey=65sh73iueso4bwrv5zjdpvn0x&dl... 

Expected output and comments:

https://www.dropbox.com/scl/fi/pwsxgbu0vr57zg3xaocmp/Expected-Output.xlsx?rlkey=7ku67o2scts81kx10fkw...

Thanks once again for your help.

BR,

Goscr80

[goscr80]

Hi Idendlin,

Thanks again for your help.  I have updated the expected output file. As i had said, the report it is a detail report, and it requires to display approx 39 columns.  5 columns  are the ones that need the expecific requirement requested under this topic or help needed, combined with other fields that come from the diferent tables, but all fields are required. 

POWER BI:

https://www.dropbox.com/scl/fi/1drketrri6bnmmz8xlmay/Examplepbi.pbix?rlkey=3um9qumjbanbx8curb2nqufbq... 

Data: 

https://www.dropbox.com/scl/fi/v60vnmp6ihrd6g9m9yj9n/Example.xlsx?rlkey=65sh73iueso4bwrv5zjdpvn0x&dl... 

Expected output and comments:

https://www.dropbox.com/scl/fi/tshyfdopwhg46dsmkaiae/Expected-Output.xlsx?rlkey=om6gftdveoq5w3ji48yg... 

Thanks once again for your help.

goscr80

[lbendlin]

The process will be the same but the DAX query obviously will be quite a bit larger.

// DAX Query
DEFINE
	VAR __DS0Core = 
	SELECTCOLUMNS(
	KEEPFILTERS(
		FILTER(
			KEEPFILTERS(
				SUMMARIZECOLUMNS(
					'SO Loop 1'[L1 Sold to Party],
					'SO Loop 2'[L2 Sold to Party],
					'SO Loop 3'[L3 Sold to Party],
					'SO Loop 1'[L1 SoldToPartyIntExt],
					'SO Loop 1'[L1 FinalExternalCustomer],
					'SO Loop 1'[L1 MDFinalExternalCustomerMarketArea],
					'SO Loop 1'[L1 SOCustomerPONoCSRNo],
					'SO Loop 1'[L1 SOCreatedOnDate],
					'SO Loop 1'[L1 SOCreatedBy],
					'SO Loop 1'[L1 SalesOrder],
					'SO Loop 1'[L1 SalesOrderItem],
					'SO Loop 1'[L1 PurchaseOrder],
					'SO Loop 1'[L1 Material],
					'SO Loop 1'[L1 MaterialDesc],
					'SO Loop 1'[L1 Plant],
					'SO Loop 1'[L1 RemainingQuantityItm],
					'SO Loop 1'[L1 RemainingNetValue_InpCurrency],
					'SO Loop 1'[L1 SOFirstDateItm],
					'SO Loop 1'[L1 Delivery],
					'SO Loop 1'[L1 Delv on],
					'SO Loop 2'[L2 SoldToPartyIntExt],
					'SO Loop 2'[L2 FinalExternalCustomer],
					'SO Loop 2'[L2 MDFinalExternalCustomerMarketArea],
					'SO Loop 2'[L2 SOCustomerPONoCSRNo],
					'SO Loop 2'[L2 SalesOrder],
					'SO Loop 2'[L2 SalesOrderItem],
					'SO Loop 2'[L2 Material],
					'SO Loop 2'[L2 MaterialDesc],
					'SO Loop 2'[L2 Plant],
					'SO Loop 2'[L2 PurchaseOrder],
					'SO Loop 2'[L2 RemainingQuantityItm],
					'SO Loop 2'[L2 RemainingNetValue_InpCurrency],
					'SO Loop 2'[L2 SOFirstDateItm],
					'SO Loop 2'[L2 SOConfMADateItm],
					'SO Loop 2'[L2 SOConfDeliveryDateItmLatest],
					'SO Loop 2'[L2 Delivery],
					'SO Loop 2'[L2 Delv on],
					'SO Loop 3'[L3 SoldToPartyIntExt],
					'SO Loop 3'[L3 FinalExternalCustomer],
					'SO Loop 3'[L3 MDFinalExternalCustomerMarketArea],
					'SO Loop 3'[L3 SOCustomerPONOCSRN0],
					'SO Loop 3'[L3 SalesOrder],
					'SO Loop 3'[L3 Sales OrderItm],
					'SO Loop 3'[L3 Material.],
					'SO Loop 3'[L3 MaterialDesc],
					'SO Loop 3'[L3 Plant],
					'SO Loop 3'[L3 Remaning QuantityItm],
					'SO Loop 3'[L3 SOFirstDateItm],
					'SO Loop 3'[L3 SOConfMADateItm],
					'SO Loop 3'[L3 SOConfDeliveryDateItemLatest],
					'SO Loop 3'[L3 Delivery],
					'SO Loop 3'[L3 Delv on],
					"CountRowsSO_Loop_3", CALCULATE(COUNTROWS('SO Loop 3'))
				)
			),
			OR(
				OR(
					OR(
						OR(
							OR(
								OR(
									OR(
										OR(
											OR(
												OR(
													OR(
														OR(
															OR(
																OR(
																	OR(
																		OR(
																			OR(
																				OR(
																					OR(
																						OR(
																							OR(
																								OR(
																									OR(
																										OR(
																											OR(
																												OR(
																													OR(
																														OR(
																															OR(
																																OR(
																																	OR(
																																		OR(
																																			OR(
																																				OR(
																																					OR(
																																						OR(
																																							OR(
																																								OR(
																																									OR(
																																										OR(
																																											OR(
																																												OR(
																																													OR(
																																														OR(
																																															OR(
																																																OR(
																																																	OR(
																																																		OR(
																																																			OR(
																																																				OR(
																																																					OR(
																																																						NOT(ISBLANK('SO Loop 1'[L1 Sold to Party])),
																																																						NOT(ISBLANK('SO Loop 2'[L2 Sold to Party]))
																																																					),
																																																					NOT(ISBLANK('SO Loop 3'[L3 Sold to Party]))
																																																				),
																																																				NOT(ISBLANK('SO Loop 1'[L1 SoldToPartyIntExt]))
																																																			),
																																																			NOT(ISBLANK('SO Loop 1'[L1 FinalExternalCustomer]))
																																																		),
																																																		NOT(ISBLANK('SO Loop 1'[L1 MDFinalExternalCustomerMarketArea]))
																																																	),
																																																	NOT(ISBLANK('SO Loop 1'[L1 SOCustomerPONoCSRNo]))
																																																),
																																																NOT(ISBLANK('SO Loop 1'[L1 SOCreatedOnDate]))
																																															),
																																															NOT(ISBLANK('SO Loop 1'[L1 SOCreatedBy]))
																																														),
																																														NOT(ISBLANK('SO Loop 1'[L1 SalesOrder]))
																																													),
																																													NOT(ISBLANK('SO Loop 1'[L1 SalesOrderItem]))
																																												),
																																												NOT(ISBLANK('SO Loop 1'[L1 PurchaseOrder]))
																																											),
																																											NOT(ISBLANK('SO Loop 1'[L1 Material]))
																																										),
																																										NOT(ISBLANK('SO Loop 1'[L1 MaterialDesc]))
																																									),
																																									NOT(ISBLANK('SO Loop 1'[L1 Plant]))
																																								),
																																								NOT(ISBLANK('SO Loop 1'[L1 RemainingQuantityItm]))
																																							),
																																							NOT(ISBLANK('SO Loop 1'[L1 RemainingNetValue_InpCurrency]))
																																						),
																																						NOT(ISBLANK('SO Loop 1'[L1 SOFirstDateItm]))
																																					),
																																					NOT(ISBLANK('SO Loop 1'[L1 Delivery]))
																																				),
																																				NOT(ISBLANK('SO Loop 1'[L1 Delv on]))
																																			),
																																			NOT(ISBLANK('SO Loop 2'[L2 SoldToPartyIntExt]))
																																		),
																																		NOT(ISBLANK('SO Loop 2'[L2 FinalExternalCustomer]))
																																	),
																																	NOT(ISBLANK('SO Loop 2'[L2 MDFinalExternalCustomerMarketArea]))
																																),
																																NOT(ISBLANK('SO Loop 2'[L2 SOCustomerPONoCSRNo]))
																															),
																															NOT(ISBLANK('SO Loop 2'[L2 SalesOrder]))
																														),
																														NOT(ISBLANK('SO Loop 2'[L2 SalesOrderItem]))
																													),
																													NOT(ISBLANK('SO Loop 2'[L2 Material]))
																												),
																												NOT(ISBLANK('SO Loop 2'[L2 MaterialDesc]))
																											),
																											NOT(ISBLANK('SO Loop 2'[L2 Plant]))
																										),
																										NOT(ISBLANK('SO Loop 2'[L2 PurchaseOrder]))
																									),
																									NOT(ISBLANK('SO Loop 2'[L2 RemainingQuantityItm]))
																								),
																								NOT(ISBLANK('SO Loop 2'[L2 RemainingNetValue_InpCurrency]))
																							),
																							NOT(ISBLANK('SO Loop 2'[L2 SOFirstDateItm]))
																						),
																						NOT(ISBLANK('SO Loop 2'[L2 SOConfMADateItm]))
																					),
																					NOT(ISBLANK('SO Loop 2'[L2 SOConfDeliveryDateItmLatest]))
																				),
																				NOT(ISBLANK('SO Loop 2'[L2 Delivery]))
																			),
																			NOT(ISBLANK('SO Loop 2'[L2 Delv on]))
																		),
																		NOT(ISBLANK('SO Loop 3'[L3 SoldToPartyIntExt]))
																	),
																	NOT(ISBLANK('SO Loop 3'[L3 FinalExternalCustomer]))
																),
																NOT(ISBLANK('SO Loop 3'[L3 MDFinalExternalCustomerMarketArea]))
															),
															NOT(ISBLANK('SO Loop 3'[L3 SOCustomerPONOCSRN0]))
														),
														... (15000 more lines)
																																						),
																																						NOT(ISBLANK('SO Loop 1'[L1 RemainingQuantityItm]))
																																					),
																																					NOT(ISBLANK('SO Loop 1'[L1 RemainingNetValue_InpCurrency]))
																																				),
																																				NOT(ISBLANK('SO Loop 1'[L1 SOFirstDateItm]))
																																			),
																																			NOT(ISBLANK('SO Loop 1'[L1 Delivery]))
																																		),
																																		NOT(ISBLANK('SO Loop 1'[L1 Delv on]))
																																	),
																																	NOT(ISBLANK('SO Loop 2'[L2 SoldToPartyIntExt]))
																																),
																																NOT(ISBLANK('SO Loop 2'[L2 FinalExternalCustomer]))
																															),
																															NOT(ISBLANK('SO Loop 2'[L2 MDFinalExternalCustomerMarketArea]))
																														),
																														NOT(ISBLANK('SO Loop 2'[L2 SOCustomerPONoCSRNo]))
																													),
																													NOT(ISBLANK('SO Loop 2'[L2 SalesOrder]))
																												),
																												NOT(ISBLANK('SO Loop 2'[L2 SalesOrderItem]))
																											),
																											NOT(ISBLANK('SO Loop 2'[L2 Material]))
																										),
																										NOT(ISBLANK('SO Loop 2'[L2 MaterialDesc]))
																									),
																									NOT(ISBLANK('SO Loop 2'[L2 Plant]))
																								),
																								NOT(ISBLANK('SO Loop 2'[L2 PurchaseOrder]))
																							),
																							NOT(ISBLANK('SO Loop 2'[L2 RemainingQuantityItm]))
																						),
																						NOT(ISBLANK('SO Loop 2'[L2 RemainingNetValue_InpCurrency]))
																					),
																					NOT(ISBLANK('SO Loop 2'[L2 SOFirstDateItm]))
																				),
																				NOT(ISBLANK('SO Loop 2'[L2 SOConfMADateItm]))
																			),
																			NOT(ISBLANK('SO Loop 2'[L2 SOConfDeliveryDateItmLatest]))
																		),
																		NOT(ISBLANK('SO Loop 2'[L2 Delivery]))
																	),
																	NOT(ISBLANK('SO Loop 2'[L2 Delv on]))
																),
																NOT(ISBLANK('SO Loop 3'[L3 SoldToPartyIntExt]))
															),
															NOT(ISBLANK('SO Loop 3'[L3 FinalExternalCustomer]))
														),
														NOT(ISBLANK('SO Loop 3'[L3 MDFinalExternalCustomerMarketArea]))
													),
													NOT(ISBLANK('SO Loop 3'[L3 SOCustomerPONOCSRN0]))
												),
												NOT(ISBLANK('SO Loop 3'[L3 SalesOrder]))
											),
											NOT(ISBLANK('SO Loop 3'[L3 Sales OrderItm]))
										),
										NOT(ISBLANK('SO Loop 3'[L3 Material.]))
									),
									NOT(ISBLANK('SO Loop 3'[L3 MaterialDesc]))
								),
								NOT(ISBLANK('SO Loop 3'[L3 Plant]))
							),
							NOT(ISBLANK('SO Loop 3'[L3 Remaning QuantityItm]))
						),
						NOT(ISBLANK('SO Loop 3'[L3 SOFirstDateItm]))
					),
					NOT(ISBLANK('SO Loop 3'[L3 SOConfMADateItm]))
				),
				NOT(ISBLANK('SO Loop 3'[L3 SOConfDeliveryDateItemLatest]))
			),
			NOT(ISBLANK('SO Loop 3'[L3 Delivery]))
		),
		NOT(ISBLANK('SO Loop 3'[L3 Delv on]))
	)
)

	VAR __DS0PrimaryShowAll = 
		SUMMARIZE(
			UNION(__DS0Core, __DS0PrimaryShowAllCompat),
			'SO Loop 1'[L1 Sold to Party],
			'SO Loop 2'[L2 Sold to Party],
			'SO Loop 3'[L3 Sold to Party],
			'SO Loop 1'[L1 SoldToPartyIntExt],
			'SO Loop 1'[L1 FinalExternalCustomer],
			'SO Loop 1'[L1 MDFinalExternalCustomerMarketArea],
			'SO Loop 1'[L1 SOCustomerPONoCSRNo],
			'SO Loop 1'[L1 SOCreatedOnDate],
			'SO Loop 1'[L1 SOCreatedBy],
			'SO Loop 1'[L1 SalesOrder],
			'SO Loop 1'[L1 SalesOrderItem],
			'SO Loop 1'[L1 PurchaseOrder],
			'SO Loop 1'[L1 Material],
			'SO Loop 1'[L1 MaterialDesc],
			'SO Loop 1'[L1 Plant],
			'SO Loop 1'[L1 RemainingQuantityItm],
			'SO Loop 1'[L1 RemainingNetValue_InpCurrency],
			'SO Loop 1'[L1 SOFirstDateItm],
			'SO Loop 1'[L1 Delivery],
			'SO Loop 1'[L1 Delv on],
			'SO Loop 2'[L2 SoldToPartyIntExt],
			'SO Loop 2'[L2 FinalExternalCustomer],
			'SO Loop 2'[L2 MDFinalExternalCustomerMarketArea],
			'SO Loop 2'[L2 SOCustomerPONoCSRNo],
			'SO Loop 2'[L2 SalesOrder],
			'SO Loop 2'[L2 SalesOrderItem],
			'SO Loop 2'[L2 Material],
			'SO Loop 2'[L2 MaterialDesc],
			'SO Loop 2'[L2 Plant],
			'SO Loop 2'[L2 PurchaseOrder],
			'SO Loop 2'[L2 RemainingQuantityItm],
			'SO Loop 2'[L2 RemainingNetValue_InpCurrency],
			'SO Loop 2'[L2 SOFirstDateItm],
			'SO Loop 2'[L2 SOConfMADateItm],
			'SO Loop 2'[L2 SOConfDeliveryDateItmLatest],
			'SO Loop 2'[L2 Delivery],
			'SO Loop 2'[L2 Delv on],
			'SO Loop 3'[L3 SoldToPartyIntExt],
			'SO Loop 3'[L3 FinalExternalCustomer],
			'SO Loop 3'[L3 MDFinalExternalCustomerMarketArea],
			'SO Loop 3'[L3 SOCustomerPONOCSRN0],
			'SO Loop 3'[L3 SalesOrder],
			'SO Loop 3'[L3 Sales OrderItm],
			'SO Loop 3'[L3 Material.],
			'SO Loop 3'[L3 MaterialDesc],
			'SO Loop 3'[L3 Plant],
			'SO Loop 3'[L3 Remaning QuantityItm],
			'SO Loop 3'[L3 SOFirstDateItm],
			'SO Loop 3'[L3 SOConfMADateItm],
			'SO Loop 3'[L3 SOConfDeliveryDateItemLatest],
			'SO Loop 3'[L3 Delivery],
			'SO Loop 3'[L3 Delv on]
		)

	VAR __DS0PrimaryWindowed = 
		TOPN(
			501,
			__DS0PrimaryShowAll,
			'SO Loop 3'[L3 MDFinalExternalCustomerMarketArea],
			1,
			'SO Loop 1'[L1 Sold to Party],
			1,
			'SO Loop 2'[L2 Sold to Party],
			1,
			'SO Loop 3'[L3 Sold to Party],
			1,
			'SO Loop 1'[L1 SoldToPartyIntExt],
			1,
			'SO Loop 1'[L1 FinalExternalCustomer],
			1,
			'SO Loop 1'[L1 MDFinalExternalCustomerMarketArea],
			1,
			'SO Loop 1'[L1 SOCustomerPONoCSRNo],
			1,
			'SO Loop 1'[L1 SOCreatedOnDate],
			1,
			'SO Loop 1'[L1 SOCreatedBy],
			1,
			'SO Loop 1'[L1 SalesOrder],
			1,
			'SO Loop 1'[L1 SalesOrderItem],
			1,
			'SO Loop 1'[L1 PurchaseOrder],
			1,
			'SO Loop 1'[L1 Material],
			1,
			'SO Loop 1'[L1 MaterialDesc],
			1,
			'SO Loop 1'[L1 Plant],
			1,
			'SO Loop 1'[L1 RemainingQuantityItm],
			1,
			'SO Loop 1'[L1 RemainingNetValue_InpCurrency],
			1,
			'SO Loop 1'[L1 SOFirstDateItm],
			1,
			'SO Loop 1'[L1 Delivery],
			1,
			'SO Loop 1'[L1 Delv on],
			1,
			'SO Loop 2'[L2 SoldToPartyIntExt],
			1,
			'SO Loop 2'[L2 FinalExternalCustomer],
			1,
			'SO Loop 2'[L2 MDFinalExternalCustomerMarketArea],
			1,
			'SO Loop 2'[L2 SOCustomerPONoCSRNo],
			1,
			'SO Loop 2'[L2 SalesOrder],
			1,
			'SO Loop 2'[L2 SalesOrderItem],
			1,
			'SO Loop 2'[L2 Material],
			1,
			'SO Loop 2'[L2 MaterialDesc],
			1,
			'SO Loop 2'[L2 Plant],
			1,
			'SO Loop 2'[L2 PurchaseOrder],
			1,
			'SO Loop 2'[L2 RemainingQuantityItm],
			1,
			'SO Loop 2'[L2 RemainingNetValue_InpCurrency],
			1,
			'SO Loop 2'[L2 SOFirstDateItm],
			1,
			'SO Loop 2'[L2 SOConfMADateItm],
			1,
			'SO Loop 2'[L2 SOConfDeliveryDateItmLatest],
			1,
			'SO Loop 2'[L2 Delivery],
			1,
			'SO Loop 2'[L2 Delv on],
			1,
			'SO Loop 3'[L3 SoldToPartyIntExt],
			1,
			'SO Loop 3'[L3 FinalExternalCustomer],
			1,
			'SO Loop 3'[L3 SOCustomerPONOCSRN0],
			1,
			'SO Loop 3'[L3 SalesOrder],
			1,
			'SO Loop 3'[L3 Sales OrderItm],
			1,
			'SO Loop 3'[L3 Material.],
			1,
			'SO Loop 3'[L3 MaterialDesc],
			1,
			'SO Loop 3'[L3 Plant],
			1,
			'SO Loop 3'[L3 Remaning QuantityItm],
			1,
			'SO Loop 3'[L3 SOFirstDateItm],
			1,
			'SO Loop 3'[L3 SOConfMADateItm],
			1,
			'SO Loop 3'[L3 SOConfDeliveryDateItemLatest],
			1,
			'SO Loop 3'[L3 Delivery],
			1,
			'SO Loop 3'[L3 Delv on],
			1
		)

EVALUATE
	__DS0PrimaryWindowed

ORDER BY
	'SO Loop 3'[L3 MDFinalExternalCustomerMarketArea],
	'SO Loop 1'[L1 Sold to Party],
	'SO Loop 2'[L2 Sold to Party],
	'SO Loop 3'[L3 Sold to Party],
	'SO Loop 1'[L1 SoldToPartyIntExt],
	'SO Loop 1'[L1 FinalExternalCustomer],
	'SO Loop 1'[L1 MDFinalExternalCustomerMarketArea],
	'SO Loop 1'[L1 SOCustomerPONoCSRNo],
	'SO Loop 1'[L1 SOCreatedOnDate],
	'SO Loop 1'[L1 SOCreatedBy],
	'SO Loop 1'[L1 SalesOrder],
	'SO Loop 1'[L1 SalesOrderItem],
	'SO Loop 1'[L1 PurchaseOrder],
	'SO Loop 1'[L1 Material],
	'SO Loop 1'[L1 MaterialDesc],
	'SO Loop 1'[L1 Plant],
	'SO Loop 1'[L1 RemainingQuantityItm],
	'SO Loop 1'[L1 RemainingNetValue_InpCurrency],
	'SO Loop 1'[L1 SOFirstDateItm],
	'SO Loop 1'[L1 Delivery],
	'SO Loop 1'[L1 Delv on],
	'SO Loop 2'[L2 SoldToPartyIntExt],
	'SO Loop 2'[L2 FinalExternalCustomer],
	'SO Loop 2'[L2 MDFinalExternalCustomerMarketArea],
	'SO Loop 2'[L2 SOCustomerPONoCSRNo],
	'SO Loop 2'[L2 SalesOrder],
	'SO Loop 2'[L2 SalesOrderItem],
	'SO Loop 2'[L2 Material],
	'SO Loop 2'[L2 MaterialDesc],
	'SO Loop 2'[L2 Plant],
	'SO Loop 2'[L2 PurchaseOrder],
	'SO Loop 2'[L2 RemainingQuantityItm],
	'SO Loop 2'[L2 RemainingNetValue_InpCurrency],
	'SO Loop 2'[L2 SOFirstDateItm],
	'SO Loop 2'[L2 SOConfMADateItm],
	'SO Loop 2'[L2 SOConfDeliveryDateItmLatest],
	'SO Loop 2'[L2 Delivery],
	'SO Loop 2'[L2 Delv on],
	'SO Loop 3'[L3 SoldToPartyIntExt],
	'SO Loop 3'[L3 FinalExternalCustomer],
	'SO Loop 3'[L3 SOCustomerPONOCSRN0],
	'SO Loop 3'[L3 SalesOrder],
	'SO Loop 3'[L3 Sales OrderItm],
	'SO Loop 3'[L3 Material.],
	'SO Loop 3'[L3 MaterialDesc],
	'SO Loop 3'[L3 Plant],
	'SO Loop 3'[L3 Remaning QuantityItm],
	'SO Loop 3'[L3 SOFirstDateItm],
	'SO Loop 3'[L3 SOConfMADateItm],
	'SO Loop 3'[L3 SOConfDeliveryDateItemLatest],
	'SO Loop 3'[L3 Delivery],
	'SO Loop 3'[L3 Delv on]

[goscr80]

Hello Ibendlin,

Thanks... could you please share the power bi file with the DAX. I am not sure but it looks to me that the pasted dax code it not right or it is incomplete since __DS0PrimaryShowAllCompat is missing.

Thanks again,

goscr80

[lbendlin]

Yes, the DAX code was over 280000 rows... I tried to give you a hint that this is not a sustainable approach.

You may want to rethink the requirements and maybe adjust them somehow.

[lbendlin]

i don't understand the expected output. The comments also don't help much.  Can you limit the sample date to just what you need and indicate which fields need to be mapped across the tables?

[lbendlin]

The equivalent DAX function is COALESCE()