I am trying to create a DAX on Union of 2 existing semantic models but am running into an error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."
I have matched the columns from both semantic model & ensured are having same number of columns & names.
UnionTable =
UNION(
SELECTCOLUMNS(
FX_Data_2022_Onwards,
"TRAN_DATE_NEW", FX_Data_2022_Onwards[TRAN_DATE], // TRAN_DATE_NEW aligned
"TRAN_DATE_OLD", FX_Data_2022_Onwards[VALUE_DATE], // TRAN_DATE_OLD aligned
"TRAN_ID", FX_Data_2022_Onwards[TRAN_ID],
"PART_TRAN_TYPE", FX_Data_2022_Onwards[PART_TRAN_TYPE],
"GL_SUB_HEAD_CODE", FX_Data_2022_Onwards[GL_SUB_HEAD_CODE],
"CIF No", FX_Data_2022_Onwards[CIF No],
"Customer Name", FX_Data_2022_Onwards[Customer Name],
"FORACID", FX_Data_2022_Onwards[FORACID],
"VALUE_DATE", FX_Data_2022_Onwards[VALUE_DATE],
"TRAN_PARTICULAR", FX_Data_2022_Onwards[TRAN_PARTICULAR],
"REF_NUM", FX_Data_2022_Onwards[REF_NUM],
"TRAN_RMKS", FX_Data_2022_Onwards[TRAN_RMKS],
"PSTD_DATE", FX_Data_2022_Onwards[PSTD_DATE],
"Time", FX_Data_2022_Onwards[Time],
"RATE_CODE", FX_Data_2022_Onwards[RATE_CODE],
"RATE", FX_Data_2022_Onwards[RATE],
"CRNCY_CODE", FX_Data_2022_Onwards[CRNCY_CODE],
"TRAN_CRNCY_CODE", FX_Data_2022_Onwards[TRAN_CRNCY_CODE],
"TRAN_AMT", FX_Data_2022_Onwards[TRAN_AMT],
"REF_CRNCY_CODE", FX_Data_2022_Onwards[REF_CRNCY_CODE],
"REF_AMT", FX_Data_2022_Onwards[REF_AMT],
"TRAN_AMT_AED", FX_Data_2022_Onwards[TRAN_AMT_AED],
"TREA_RATE", FX_Data_2022_Onwards[TREA_RATE],
"CCY SLABS", FX_Data_2022_Onwards[CCY SLABS],
"Type", FX_Data_2022_Onwards[Type],
"MID Rate in USD", FX_Data_2022_Onwards[MID Rate in USD],
"Ccy pair", FX_Data_2022_Onwards[Ccy pair],
"Base currency", FX_Data_2022_Onwards[Base currency],
"Base currency Amount", FX_Data_2022_Onwards[Base currency Amount],
"Tran rate (1 Base ccy = x Other ccy)", FX_Data_2022_Onwards[Tran rate(1 Base ccy = x Other ccy)],
"Check", FX_Data_2022_Onwards[Check],
"Other Ccy", FX_Data_2022_Onwards[Other Ccy],
"Other Ccy Amount", FX_Data_2022_Onwards[Other Ccy Amount],
"REVERSAL", FX_Data_2022_Onwards[REVERSAL],
"Buy/Sell", FX_Data_2022_Onwards[Buy/Sell],
"Derived real-time rate", FX_Data_2022_Onwards[Derived real-time rate],
"Currency other than AED", FX_Data_2022_Onwards[Currency other than AED],
"Amount other than AED", FX_Data_2022_Onwards[Amount other than AED],
"Seg profit 1", FX_Data_2022_Onwards[Seg profit 1],
"Real time profit-1", FX_Data_2022_Onwards[Real time profit-1],
"Currency other than USD", FX_Data_2022_Onwards[Currency other than USD],
"Amount other than USD", FX_Data_2022_Onwards[Amount other than USD],
"Seg profit 2", FX_Data_2022_Onwards[Seg profit 2],
"Real time profit-2", FX_Data_2022_Onwards[Real time profit-2],
"Segment Profit (AED)", FX_Data_2022_Onwards[Segment Profit(AED)],
"Profit w.r.t real time rate (AED)", FX_Data_2022_Onwards[Profit w.r.t real time rate(AED)],
"Difference", FX_Data_2022_Onwards[Difference],
"GL Segment", FX_Data_2022_Onwards[GL Segment],
"GL Subsegment", FX_Data_2022_Onwards[GL Subsegment],
"Sol ID", FX_Data_2022_Onwards[Sol ID],
"SOL Desc", FX_Data_2022_Onwards[SOL Desc],
"RM Name", FX_Data_2022_Onwards[RM Name],
"DELIVERY_CHANNEL_ID", FX_Data_2022_Onwards[DELIVERY_CHANNEL_ID],
"Source", "FX_Data_2022_Onwards"
),
SELECTCOLUMNS(
RMT_CPR,
"TRAN_DATE_NEW", RMT_CPR[VALUE_DATE], // Match the first table's TRAN_DATE_NEW
"TRAN_DATE_OLD", RMT_CPR[TRAN_DATE], // Match the first table's TRAN_DATE_OLD
"TRAN_ID", RMT_CPR[TRAN_ID],
"PART_TRAN_TYPE", RMT_CPR[PART_TRAN_TYPE],
"GL_SUB_HEAD_CODE", RMT_CPR[GL_SUB_HEAD_CODE],
"CIF No", RMT_CPR[CIF No],
"Customer Name", RMT_CPR[Customer Name],
"FORACID", RMT_CPR[FORACID],
"VALUE_DATE", RMT_CPR[VALUE_DATE],
"TRAN_PARTICULAR", RMT_CPR[TRAN_PARTICULAR],
"REF_NUM", RMT_CPR[REF_NUM],
"TRAN_RMKS", RMT_CPR[TRAN_RMKS],
"PSTD_DATE", RMT_CPR[PSTD_DATE],
"Time", RMT_CPR[Time],
"RATE_CODE", RMT_CPR[RATE_CODE],
"RATE", RMT_CPR[RATE],
"CRNCY_CODE", RMT_CPR[CRNCY_CODE],
"TRAN_CRNCY_CODE", RMT_CPR[TRAN_CRNCY_CODE],
"TRAN_AMT", RMT_CPR[TRAN_AMT],
"REF_CRNCY_CODE", RMT_CPR[REF_CRNCY_CODE],
"REF_AMT", RMT_CPR[REF_AMT],
"TRAN_AMT_AED", RMT_CPR[TRAN_AMT_AED],
"TREA_RATE", RMT_CPR[TREA_RATE],
"CCY SLABS", RMT_CPR[CCY SLABS],
"Type", RMT_CPR[Type],
"MID Rate in USD", RMT_CPR[MID Rate in USD],
"Ccy pair", RMT_CPR[Ccy pair],
"Base currency", RMT_CPR[Base currency],
"Base currency Amount", RMT_CPR[Base currency Amount],
"Tran rate (1 Base ccy = x Other ccy)", RMT_CPR[Tran rate(1 Base ccy = x Other ccy)],
"Check", RMT_CPR[Check],
"Other Ccy", RMT_CPR[Other Ccy],
"Other Ccy Amount", RMT_CPR[Other Ccy Amount],
"REVERSAL", RMT_CPR[REVERSAL],
"Buy/Sell", RMT_CPR[Buy/Sell],
"Derived real-time rate", RMT_CPR[Derived real-time rate],
"Currency other than AED", RMT_CPR[Currency other than AED],
"Amount other than AED", RMT_CPR[Amount other than AED],
"Seg profit 1", RMT_CPR[Seg profit 1],
"Real time profit-1", RMT_CPR[Real time profit-1],
"Currency other than USD", RMT_CPR[Currency other than USD],
"Amount other than USD", RMT_CPR[Amount other than USD],
"Seg profit 2", RMT_CPR[Seg profit 2],
"Real time profit-2", RMT_CPR[Real time profit-2],
"Segment Profit (AED)", RMT_CPR[Segment Profit(AED)],
"Profit w.r.t real time rate (AED)", RMT_CPR[Profit w.r.t real time rate(AED)],
"Difference", RMT_CPR[Difference],
"GL Segment", RMT_CPR[GL Segment],
"GL Subsegment", RMT_CPR[GL Subsegment],
"Sol ID", RMT_CPR[Sol ID],
"SOL Desc", RMT_CPR[SOL Desc],
"RM Name", RMT_CPR[RM Name],
"DELIVERY_CHANNEL_ID", RMT_CPR[DELIVERY_CHANNEL_ID],
"Source", "RMT_CPR"
)
)
