Calculated Column that might pull multiple values

[Beth_H]

Hi there,

I am trying to create a table which pools information from another table but orders in a different way.

Below is the UserFieldValue table. 

UserFieldValue
RecordID	UserFieldID	UserFieldName	Value
1	855	Agency	Blue
1	700	Department	Black
2	700	Department	Yellow
2	855	Agency	Blue
3	677	Block	Green
3	855	Agency	Grey
3	855	Agency	Yellow
4	677	Block	Green
4	955	Custom	Red
5	855	Agency	Blue
5	700	Department	Blue
5	650	Bench	Black

Using this data, I want to create a table that has primary key 'RecordID' and shows each UserFieldName as a column where the cells are the Values, and recordIDs with multiple inputs for a UserField show "Multiple Input":

RecordID	Agency	Department	Block	Bench	Custom
1	Blue	Black
2	Blue	Yellow
3	Multiple Input		Green
4			Green		Red
5	Blue	Blue		Black

I have tried creating a table of unique IDs and then adding calculated columns using the below but this seems to just show Multiple Input for every single RecordID

Agency = CALCULATE(

    SELECTEDVALUE(UserFieldValue[All_UserFieldValue], "Multiple Input"),

    FILTER(UserFieldValue, UserFieldValue[UserFieldId]=855)

)

Does anyone have any ideas as to where I'm going wrong?

[HotChilli]

selectedvalue is going to return the "Multiple Input" if the UserFieldValue[All_UserFieldValue] is blank OR there are multiple values so this may require more work.

However, the main problem is that the RecordID is not passed to the FILTER statement so it will be looking for userfieldid = 855 without the specific ID.  So you can probably  just add that in to the search clauses (&& UserFieldValue[RecordID] = thisTable[RecordID] )

--

Also the column seems to have 2 different names (value or All_userFieldValue)

[Beth_H]

The different value names are because I was simplifying the data for the sake of this post and missed an alteration..

But you're quite right! The FILTER did need specification on the recordid, have since adjusted:

Agency = CALCULATE(

    SELECTEDVALUE(UserFieldValue[Value]),

    FILTER(UserFieldValue, UserFieldValue[RecordId]='Custom Field Reporting'[Id] && UserFieldValue[UserFieldId]=855)

)

However still faced with the issue that when an ID has multiple input values, the SELECTEDVALUE returns a blank