Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Create a table with multiple columns from other tables

Hi
@amitchandak
I need to create a table based on below tables as shown on relation ship diagram: The table should hold the
load date, profile id, source id, source name, edg id and using below DAX expression:
 
Table = CALCULATETABLE(
SELECTCOLUMNS(LKP_PROFILE,"Load Date", values(EDG_COUNT_STATUS[LOAD_DATE]),
"Profile Name",LKP_PROFILE[PROFILE_NAME],
"EDG_ID", VALUES(EDG_COUNT_STATUS[EDG_ID])
),
filter(LKP_SOURCE, LKP_SOURCE[SOURCE_ID]=1),
filter(LKP_PROFILE, LKP_PROFILE[PROFILE_ID]=75)
)

 

But it is giving me below error:

A table of multiple values was supplied where a single value was expected

 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous 

    Why are you using values(EDG_COUNT_STATUS[LOAD_DATE])

    why not (EDG_COUNT_STATUS[LOAD_DATE])

    ?

     

    Appreciate your Kudos.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi,

      if I am using below expression:

      Table = CALCULATETABLE(
      SELECTCOLUMNS(LKP_PROFILE,"Load Date", EDG_COUNT_STATUS[LOAD_DATE],
      "Profile Name",LKP_PROFILE[PROFILE_NAME],
      "EDG_ID", VALUES(EDG_COUNT_STATUS[EDG_ID])
      ),
      filter(LKP_SOURCE, LKP_SOURCE[SOURCE_ID]=1),
      filter(LKP_PROFILE, LKP_PROFILE[PROFILE_ID]=75)
      )
       
      it gives me the below error:
      A single value for column 'LOAD_DATE' in table 'EDG_COUNT_STATUS' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

       

      • v-frfei-msft's avatar
        v-frfei-msft
        Community Support
        6 years ago

        Hi Anonymous ,

         

        Please try to update the formula as below.

        Table =
CALCULATETABLE (
    SELECTCOLUMNS (
        LKP_PROFILE,
        "Load Date", VALUES ( EDG_COUNT_STATUS[LOAD_DATE] ),
        "Profile Name", LKP_PROFILE[PROFILE_NAME],
        "EDG_ID", CONCATENATEX ( EDG_COUNT_STATUS, [EDG_ID], " " )
    ),
    FILTER ( LKP_SOURCE, LKP_SOURCE[SOURCE_ID] = 1 ),
    FILTER ( LKP_PROFILE, LKP_PROFILE[PROFILE_ID] = 75 )
)

         

        If it doesn't meet your requirement,  kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.