Combining Multiple Rows of data into one row.

[MrBlackEyE31]

Hello,

I have watched some videos and searched for some answers to my question, I have gotten close but haven't been able to get what I am trying to get. I have a table imported with multiple rows that looks somewhat similar to this

Number	Type	Location
1	Primary	Orange - New York
1	Primary	Syracus - New York
1	Secondary	Buffalo - New York
1	Secondary	Rochester - New York
1	Secondary	Ithica - New York
2	Primary	Burlington - Vermont
2	Secondary	Stowe - Vermont
2	Secondary	Rutland - Vermont
2	Secondary	Bennington - Vermont

and I am looking to get this as a result

Number	Primary Location	Secondary Location
1	Orange - New York, Syracus - New York	Buffalo - New York, Rochester - New York, Ithica - New York
2	Burlington - Vermont	Stowe - Vermont, Rutland - Vermont, Bennington - Vermont

Thank you for your time. 

[Arul]

@MrBlackEyE31 ,

try these two measures,

Primary Location = CALCULATE(
    CONCATENATEX('Table','Table'[Location]),'Table'[Type] = "Primary")

Secondary Location = CALCULATE(
    CONCATENATEX('Table','Table'[Location]),'Table'[Type] = "Secondary")

[MrBlackEyE31]

Thank you for your reply,  This gave me a column separating Primary locations and Secondary locations but did not combine them into 1 row. 

[Arul]

@MrBlackEyE31 ,

Do you need this?

Combine = CONCATENATEX('Table','Table'[Location])

[AnalyticPulse]

hello @MrBlackEyE31 
try this dax in calculated table;
CalculatedTable =
SELECTCOLUMNS(
SUMMARIZE('YourTable', 'YourTable'[Number]),
"Number", 'YourTable'[Number],
"Primary Location", CONCATENATEX(FILTER('YourTable', 'YourTable'[Type] = "Primary"), 'YourTable'[Location], ", "),
"Secondary Location", CONCATENATEX(FILTER('YourTable', 'YourTable'[Type] = "Secondary"), 'YourTable'[Location], ", ")
)

Learn Power BI free:

https://analyticpulse.blogspot.com

Learn Power BI free

Commercial Real Estate Portfolio Overview by DataFreedom

Superstore Sales 2022 vs 2023: Year-on-Year Performance Comparison dashboard

Case Study: PowerBI Dashboard Developer for Sales Visualization

Case Study: Banking and Finance dashboard (Step by step guide)

Powerbi Visualisation

[MrBlackEyE31]

Thank you for your help!

This is close, but it gave me back everything instead of the "Correct" options. 

I want it to look like this

Number	Primary Location	Secondary Location
1	Orange - New York, Syracus - New York	Buffalo - New York, Rochester - New York, Ithica - New York
2	Burlington - Vermont	Stowe - Vermont, Rutland - Vermont, Bennington - Vermont

The solution you provided gave me something like this

Number	Primary Location	Secondary Location
1	Orange - New York, Syracus - New York, Burlington - Vermont	Buffalo - New York, Rochester - New York, Ithica - New York, Stowe - Vermont, Rutland - Vermont, Bennington - Vermont
2	Orange - New York, Syracus - New York, Burlington - Vermont	Buffalo - New York, Rochester - New York, Ithica - New York, Stowe - Vermont, Rutland - Vermont, Bennington - Vermont

This is the closest I have come to getting what I want so you are on the right path here, Thank you!

[gmsamborn · Accepted solution]

Hi @MrBlackEyE31 

In @AnalyticPulse 's table, wrap the CONCATENATEX functions with CALCULATE.

CalculatedTable 2 = 
ADDCOLUMNS(
    SUMMARIZE(
        'Table',
        'Table'[Number]
    ),
    "Primary Location",
        CALCULATE(
            CONCATENATEX(
                FILTER(
                    'Table',
                    'Table'[Type] = "Primary"
                ),
                'Table'[Location],
                ", "
            )
        ),
    "Secondary Location",
        CALCULATE(
            CONCATENATEX(
                FILTER(
                    'Table',
                    'Table'[Type] = "Secondary"
                ),
                'Table'[Location],
                ", "
            )
        )
)

As an alternative, you could use @Arul 's measures like this.

CalculatedTable 3 = 
SUMMARIZE(
    'Table',
    'Table'[Number],
    "Primary",
        [Primary Location],
    "Secondary",
        [Secondary Location]
)

I would prefer the first option since it isn't really a best practice to use measures while defining calculated columns or calculated tables.

MrBlackEyE31.pbix

[MrBlackEyE31]

Thank you for your response. This worked and did exactly what I wanted. Thanks again!