CODEWITHSUNDEEP
calculated-columnspowerbidax
eclairs
eclairs

Reputation: 1695

Power BI: Combining cells from a column where given a condition

I have to perform a task in power BI (DAX) where i have a list of users and their fields of work. I have to combine all the fields corresponding to every user and find the highest frequency combination.
Below is how I was planning to do it. How can i generate the "Expected Result" from the "Sample" file? 

Sample:
    ID     Value
    a      medicine
    b      automobile
    c      banking
    d      scientist
    a      banking
    a      scientist
    d      banking

Expected Result:
ID      Value Combi
a       medicine|banking|scientist
b       automobile
c       banking
d       scientist|banking

Upvotes: 0

Views: 3214

Answers (1)

alejandro zuleta
alejandro zuleta

Reputation: 14108

You can concatenate values from multiple rows and generate a delimited string by using CONCATENATEX() DAX function. Then use ADDCOLUMNS and SUMMARIZE to get the desired result.

Expected Table =
SUMMARIZE (
    ADDCOLUMNS (
        'Table',
        "Combined Value", CONCATENATEX (
            FILTER (
                SUMMARIZE ( 'Table', 'Table'[ID], [Value] ),
                [ID] = EARLIER ( 'Table'[ID] )
            ),
            'Table'[Value],
            "|"
        )
    ),
    [ID],
    [Combined Value]
)

It produces:

enter image description here

Break down the logic

The easiest way in my opinion is create a calculated column in your table to generate the combined value column:

Combined Value =
CONCATENATEX (
    FILTER (
        SUMMARIZE ( 'Table', 'Table'[ID], [Value] ),
        [ID] = EARLIER ( 'Table'[ID] )
    ),
    'Table'[Value],
    "|"
)

Then you can create a summarized table based on your table with the previously created calculated column. To create a calculated table go to the Modeling tab and click the New Table icon.

enter image description here

Use this expression:

Resumed Table =
SUMMARIZE ( 'Table', [ID], 'Table'[Combined Value] )

You will get a table like this:

enter image description here

While both methods work just fine, the recommend way to approach this issue is directly from Power Query or from your source.

Let me know if this helps.

Upvotes: 3

Related Questions