Unpacking a Dataverse Multi-Select field in Fabric.

In my contact record, I've created a custom multi-select choice field, "Channel Activities".

I want to use this field in my report, but the SQL endpoint in Fabric only returns a ';'-delimited list of numeric values.

SELECT  Base.contactid
      , Base.pbi_channelactivities
    FROM contact AS Base
    WHERE
        Base.pbi_channelactivities IS NOT NULL;        

Returns

I need to convert these numeric values to their corresponding string values.

I'll show you how I got to the answer first - skip to the end if you just want the query.

First, I need to split the concatenated list of numeric choices into individual values using the SQL function string_split. (the values are delimited with ';')

SELECT  Base.contactid
      , string.value
    FROM [dbo].[contact]                                        AS Base
    CROSS APPLY string_split (Base.pbi_channelactivities , ';') AS string
    WHERE
        Base.pbi_channelactivities IS NOT NULL;        

That produces a list where each of the activities is on its own row - with the contactid repeated once for each of the choices stored in the original string.

Second: I'll expand the query and join this new 'value' column to the stringmap table to get the corresponding string values.

SELECT  Base.contactid
      , string.value
      , contact_channelactivity.value  AS channelactivities_string
    FROM [dbo].[contact]               AS Base
    CROSS APPLY string_split (Base.pbi_channelactivities , ';') AS string
    JOIN stringmap contact_channelactivity
        ON  contact_channelactivity.attributename = 'pbi_channelactivities'
        AND contact_channelactivity.objecttypecode = 'contact'
        AND contact_channelactivity.langid = 1033
        AND contact_channelactivity.attributevalue = string.value
    WHERE
        Base.pbi_channelactivities IS NOT NULL;        

The stringmap needs the attribute name, in this case 'pbi_channelactivities' from the contact record. It then needs the objecttypecode (the entity name) - the langid (1033 = US English, but sub in for whatever language you're using.) and finally the attribute value, 'value' - which is the previously extracted value out of the pbi_channelactivities field.

This gives us the value-label pairs for each record.

The next step is to re-aggregate the strings back into a comma separated list in a new result field. We do that with the SQL 'string_agg' function - we need to give it a 'group-by' value as well. (I'm dropping the numeric values in this example for now.)

SELECT  Base.contactid
      , STRING_AGG(contact_channelactivity.value, ', ') AS    channelactivities_string
    FROM [dbo].[contact] AS Base
    CROSS APPLY string_split (Base.pbi_channelactivities , ';') AS string
    JOIN stringmap contact_channelactivity
        ON  contact_channelactivity.attributename = 'pbi_channelactivities'
        AND contact_channelactivity.objecttypecode = 'contact'
        AND contact_channelactivity.langid = 1033
        AND contact_channelactivity.attributevalue = string.value
    WHERE
        Base.pbi_channelactivities IS NOT NULL
    GROUP BY
        Base.contactid;        

I now have a comma separated list of the selected options in the multi-select field:

I could use this as a subquery as part of my report, but I'd like to clean it up a bit and make it a pattern for re-use. - I'm moving it into a CTE (Common Table Expression) - I can have multiple CTEs in a query and each one could handle a specific multi-select field.

WITH CTE_contact_channelactivity
AS (
SELECT Base.contactid
, STRING_AGG(contact_channelactivity.value, ', ') AS channelactivities_string
FROM [dbo].[contact] AS Base
CROSS APPLY string_split (Base.pbi_channelactivities , ';') AS string
    JOIN stringmap contact_channelactivity
    ON  contact_channelactivity.attributename = 'pbi_channelactivities'
    AND contact_channelactivity.objecttypecode = 'contact'
    AND contact_channelactivity.langid = 1033
    AND contact_channelactivity.attributevalue = string.value
WHERE Base.pbi_channelactivities IS NOT NULL
GROUP BY Base.contactid
)
SELECT Base.contactid
    , Base.fullname AS [Contact Name]
    , CTE_contact_channelactivity.channelactivities_string AS [Channel Activities]
FROM [dbo].[contact] AS Base
JOIN CTE_contact_channelactivity
    ON  CTE_contact_channelactivity.contactid = Base.contactid
WHERE Base.pbi_channelactivities IS NOT NULL;        

(The SQL formatting gets a bit messy when pasted into LinkedIn - so here's a picture of the query and the results.)

There are probably multiple ways to solve for this, but this is how I approached it. Let me know if you solve it a different way

Scott.