DAX to substitute IDs with dimension table names for multichoice cells

[Elisa_H3]

Hi,

I have multichoice fields in my data, which in a normalised data model will show as IDs in one cell. In our CRM we have e.g. a multichoice question about competitors and depending on how people have filled in the question - it will provide one or multiple values. So one cell in my fact table can have values like "5" or "1", which are the IDs that have a relationship to a dimension table. To see the actual competitor names, I have a relationship between the fact table and dimension table - through these IDs.

Now if there is only one value, the competitor name can easily be added to a table chart through the relationship. However some cells can have multiple IDs - e.g. {1,3,7}. Now I would like to create a dax that would look up the name from the dimension table and return a value as {competitorname1, competitorname3, competitorname7, which does not work through the relationship. So basically I would like a dax that returns the name instead of the ID (in one cell - as they are in the fact table). So just to substitute the number.

How would that work? Thanks!

[gmsamborn]

Hi  @Elisa_H3 

If you have a fact table ( 'Fact' ) and a dimension table ( 'DimCompetitor' ), I'm assuming they are related. The relationship IS NOT being used in this solution.

My solution is using a calculated column instead of a measure. IF you want it to be dynamic, I'll probably need more details (ie. slicers, filters, etc).

My calculated column is as follows:

Concatenated Competitor Names = 
VAR RawList = SUBSTITUTE ( 'Fact'[Competitors], " ", "" )
RETURN
    CONCATENATEX (
        FILTER (
            'DimCompetitor',
            CONTAINSSTRING (
                "," & RawList & ",",
                "," & FORMAT ( 'DimCompetitor'[CompetitorID], "0" ) & ","
            )
        ),
        'DimCompetitor'[Name],
        ", "
    )

Let me know if you have any questions.

[DataNinja777]

Hi @Elisa_H3 ,

You can achieve this using the CONCATENATEX function in DAX to replace the IDs in your multichoice field with their corresponding names from the dimension table. Assuming your fact table is named FactTable and contains a column CompetitorIDs, which stores the IDs as a comma-separated string (e.g., "1,3,7"), and your dimension table CompetitorTable has CompetitorID and CompetitorName, you can use the following DAX measure:

Competitor Names = 
VAR IdsString = SELECTEDVALUE(FactTable[CompetitorIDs])
VAR IdsTable = 
    FILTER(
        CompetitorTable, 
        CONTAINSSTRING(IdsString, CompetitorTable[CompetitorID])
    )
RETURN 
    CONCATENATEX(IdsTable, CompetitorTable[CompetitorName], ", ")

This measure first retrieves the value of CompetitorIDs from the fact table. Then, it filters CompetitorTable to match all rows where the CompetitorID appears within the comma-separated CompetitorIDs string. Finally, CONCATENATEX is used to concatenate the competitor names into a single string, separated by commas. This approach works as long as CompetitorIDs in FactTable and CompetitorID in CompetitorTable are stored as text. Let me know if you need any modifications!

Best regards,

[Elisa_H3]

Thanks, I added an IF here in case the cell is blank:

Competitors_multichoice =
VAR IdsString = SELECTEDVALUE(SalesOpportunities[competitor_ID])
RETURN
IF(
ISBLANK(IdsString) || IdsString = "",
BLANK(),
VAR IdsTable =
FILTER(
COMPETITORS_LIST,
CONTAINSSTRING(IdsString, COMPETITORS_LIST[id])
)
RETURN
CONCATENATEX(IdsTable, COMPETITORS_LIST[name], ", ")
)

However what it does now is that if there is a single value, such as "17" - it will return the names for 17, 1 and 7. 

[v-bmanikante]

Hi @Elisa_H3 ,

Thank you for reaching out to Microsoft Fabric Community Forum.

@DataNinja777 Thank you for your quick response.

@Elisa_H3 Please try the below DAX measure:

Competitors_multichoice =

VAR IdsString = SELECTEDVALUE(SalesOpportunities[competitor_ID])

RETURN

IF (

    ISBLANK(IdsString) || IdsString = "",

    BLANK(),

    VAR IdsTable =

        SELECTCOLUMNS(

            ADDCOLUMNS(

                GENERATESERIES(1, 999), 

                "IDString",

                TRIM(MID(

                    SUBSTITUTE("," & IdsString & ",", ",", REPT(" ", 999)),

                    ([Value] - 1) * 999 + 1,

                    999

                ))

            ),

            "ID", VALUE([IDString])

        )

    VAR MatchedNames =

        FILTER(

            COMPETITORS_LIST,

            COMPETITORS_LIST[id] IN SELECTCOLUMNS(IdsTable, "id", [ID])

        )

    RETURN

        CONCATENATEX(MatchedNames, COMPETITORS_LIST[name], ", ")

)

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos " – I’d truly appreciate it!

Regards,

B Manikanteswara Reddy

[v-bmanikante]

Hi @Elisa_H3 ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Please don't forget to give a "Kudos " – I’d truly appreciate it!

Regards,

B Manikanteswara Reddy

[v-bmanikante]

Hi @Elisa_H3 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Please don't forget to give a "Kudos " – I’d truly appreciate it!

Regards,

B Manikanteswara Reddy

[v-bmanikante]

Hi @Elisa_H3 ,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Please don't forget to give a "Kudos " – I’d truly appreciate it!

Regards,

B Manikanteswara Reddy