Measure that concatenate 2 times

[Nicolas__]

Hello, 

I am really stuck on this one.

I have the following table ("_Risk map"): 

Risk profile	Entity
RP1	E1
RP1	E1
RP1	E1
RP1	E2
RP1	E3
RP2	E1
RP2	E1
RP2	E1
RP2	E2
RP2	E5
RP3	E3
RP4	E1

And I am looking ton create a measure that returns the following text: 

RP1 (E1, E2, E3)
RP2 (E1, E2, E5)
RP3 (E3)
RP4 (E1)

It looks like some kind of double concatenate.

I've tried multiple measures, but I always end up with results like this: 

RP1 (E1, E2, E3, E5)
RP1 (E1, E2, E3, E5)
RP1 (E1, E2, E3, E5)
RP1 (E1, E2, E3, E5)
RP2 (E1, E2, E3, E5)
RP2 (E1, E2, E3, E5)
RP2 (E1, E2, E3, E5)
RP3 (E1, E2, E3, E5)
RP4 (E1, E2, E3, E5)

Do you guys have an idea?

Thanks a lot! 

FYI my code looks like the following: 

VAR __Table         = CALCULATETABLE('_Risk map')
VAR __TableRisks    = SUMMARIZE(__Table, '_Risk map'[Risk profile], '_Risk map'[Acronym (calc)])
RETURN
CONCATENATEX(
    __TableRisks,
    '_Risk map'[Risk profile] & " (" & CALCULATE(CONCATENATEX(FILTER(__TableRisks, '_Risk map'[Risk profile] = '_Risk map'[Risk profile]), '_Risk map'[Acronym (calc)], ", ")) & ")",
    UNICHAR(10)
)

[Nicolas__]

Hey @Ahmedx @mtayyab07 

Thanks a lot for trying to help.

Unfortunately, I run accross the same issue as I encountered: the entity is repeated by the number of times it's existing in the table, and it's the same no matter the "risk profile" associated: 

VAR __Table      = CALCULATETABLE(SUMMARIZE('_Risk map', '_Risk map'[Risk profile], '_Risk map'[Acronym (calc)]))
VAR __TableRisks = SUMMARIZE(DISTINCT(__Table), '_Risk map'[Risk profile], "Combined entities", CONCATENATEX(DISTINCT(__Table), '_Risk map'[Acronym (calc)], ", "))
RETURN
    CONCATENATEX(__TableRisks, '_Risk map'[Risk profile] & " - " & [Combined entities], UNICHAR(10))

 I won't go into details, but it needs to go via a measure.

Do you have any idea on how to solve this?

Thanks again

[Ahmedx]

pls try this

[Nicolas__]

Hey @Ahmedx 

In fact, after trying, it seems that when I go via a table, your code works.

But when I use a variable as an input, it doesn't anymore.

Does that help trying to figure out the issue?

VAR __Tab = SUMMARIZE('_Risk map', '_Risk map'[Risk profile], '_Risk map'[Acronym (calc)])
RETURN
SUMMARIZE(
    DISTINCT( __Tab ),
    '_Risk map'[Risk profile],
    "Comp",
        CONCATENATEX(
            DISTINCT( __Tab ),
            '_Risk map'[Acronym (calc)],
            ", "
        )
)

[Ahmedx]

Is this what you are looking for?

[Ahmedx]

I don’t understand what you are doing there, show me the file, or explain in detail what you are doing

[Nicolas__]

Hey  @Ahmedx 

Please find attached the PBI file.

You'll see that the result ends up with: 

Although it should have been the following: 

CM - CE, EE

DB - EE, CE, VE (and no duplicated there)

EAFP - EE, CE, VE 

IAR - CE, EE

Etc.

Thanks a lot!!

Test.pbix 

(I've put a link as I can't figure out how to upload PBIX files, I have a blocking error)

[Ahmedx]

pls try this

[Nicolas__]

Thanks @Ahmedx 

Indeed that's the idea, but the whole tab needs to be in one single text, not a table (as in the file i've sent you). I'm trying to make it work but I still run across the issues preivously mentionned

[Ahmedx]

I don't think it's possible to do this in power bi

[Nicolas__]

@Ahmedx understood, thanks a lot for your help anyhow 🙂 (and it's reassuring that I don't managed to do it then!)

[Ahmedx]

I can’t download the file, it asks for login and password

[Nicolas__]

Sorry didn't know... I've uploaded it to WeTransfer instead:

PBIX file 

[mtayyab07]

You need to create a new table from your source table like this:

Grouped Table = 
SUMMARIZE(
    '_Risk map (2)', 
    '_Risk map (2)'[Risk profile], 
    "Combined Entities", CONCATENATEX('_Risk map (2)', '_Risk map (2)'[Entity], ", ")
)

OR, it's better to do this in Power Query for performance reasons:

let
    Source = Excel.Workbook(File.Contents("C:\Users\aliom\OneDrive\Desktop\Book1.xlsx"), null, true),
    #"_Risk map_Sheet" = Source{[Item="_Risk map",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(#"_Risk map_Sheet",{{"Column1", type text}, {"Column2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Risk profile", type text}, {"Entity", type text}}),

    // Group by "Risk profile" and concatenate "Entity" values
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Risk profile"}, {
        {"All Entities", each Text.Combine([Entity], ", "), type text}
    })
in
    #"Grouped Rows"