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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Smitesh
Regular Visitor

Union between 2 semantic models

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.

 

Below is the union

 

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"
    )
)


1 ACCEPTED SOLUTION
Rupak_bi
Super User
Super User

hi @Smitesh 
the error you are getting as you are trying to get a table out put in a measure. If you are trying to create a calculated table, then go to the modelling table and select table and then put the DAX to get a union table (hope your DAX is correct). Measure returns only a scaler value and will not work here.



Regards
Rupak
FOLLOW ME : https://www.linkedin.com/in/rupaksar/

View solution in original post

1 REPLY 1
Rupak_bi
Super User
Super User

hi @Smitesh 
the error you are getting as you are trying to get a table out put in a measure. If you are trying to create a calculated table, then go to the modelling table and select table and then put the DAX to get a union table (hope your DAX is correct). Measure returns only a scaler value and will not work here.



Regards
Rupak
FOLLOW ME : https://www.linkedin.com/in/rupaksar/

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.