CONCATENATEX Empty Records

[SabineOussi]

Hello Community,

I am trying to create a measure that concatenates both columns and measure and it seems that I am missing a grouping somewhere so it is not returning the desired output.

Here is my available data

This is the concatenation measure I have created

CONCAT = 
CONCATENATEX(
    VALUES('Table'[Column1]), 'Table'[Column1] &" ("&
    CONCATENATEX(
        VALUES('Table'[Column2]),
        'Table'[Column2] &" "& FORMAT([Measure1], "0%") &" ["& [Measure2] &"]",
        ", ",
        'Table'[Column2], ASC
    ) &")", "
    ")

It is returning results for records that don't exist, for example:
- Area1 Section3 doesn't have any Column2 with values A, B, C, or D.

- Area2 Section4 doesn't have any Column2 with value A or K.

Adding a condition on either measure1 or measure2 didn't solve the issue.

Any thoughts on this?

Thank you,

Sabine O.

[AlB · Accepted solution]

Hi @SabineOussi 

What would the expected result be?

Try this

CONCAT =
CONCATENATEX (
    VALUES ( 'Table'[Column1] ),
    'Table'[Column1] & " ("
        & CALCULATE (
            CONCATENATEX (
                VALUES ( 'Table'[Column2] ),
                'Table'[Column2] & " "
                    & FORMAT ( [Measure1], "0%" ) & " [" & [Measure2] & "]",
                ", ",
                'Table'[Column2], ASC
            )
        ) & ")",
    "
"
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

@SabineOussi 

Ok. It's clear now. It took some changing. Try this:

CONCAT7 =
CONCATENATEX (
    FILTER (
        ADDCOLUMNS (
            VALUES ( 'Table'[Column1] ),
            "aux_",
                CALCULATE (
                    CONCATENATEX (
                        FILTER ( VALUES ( 'Table'[Column2] ), [Measure1] <= 0.03 ),
                        'Table'[Column2] & " "
                            & FORMAT ( [Measure1], "0%" ) & " [" & [Measure2] & "]",
                        ", ",
                        'Table'[Column2], ASC
                    )
                )
        ),
        [aux_] <> ""
    ),
    'Table'[Column1] & " (" & [aux_] & ")",
    UNICHAR ( 10 )
)

Also note that I've used UNICHAR(10) for the line break, which I find more convenient.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[SabineOussi]

Perfect.

It returns exactly what I want.

Thanks a lot!
Sabine O.

[AlB]

@SabineOussi 

Try this. You might have to do some minor changes for it to show exactly how you want (spaces, what happens when measure 1 is not shown, etc.) By the way, can you share the pbix? There's something that I would like to try

CONCAT =
CONCATENATEX (
    VALUES ( 'Table'[Column1] ),
    'Table'[Column1] & " ("
        & CALCULATE (
            CONCATENATEX (
                VALUES ( 'Table'[Column2] ),
                'Table'[Column2] & " "
                    & IF ( [Measure1] <= 0.03, FORMAT ( [Measure1], "0%" ) ) & " [" & [Measure2] & "]",
                ", ",
                'Table'[Column2], ASC
            )
        ) & ")",
    "
"
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[SabineOussi]

Thanks a lot for your help, the condition added doesn't work on all columns.

You can find the sample pbix here

CONCAT: my initial calculation
CONCAT2: empty records removed
CONCAT3: with condition

I'm guessing you want to try variables? 

Thanks again,

Sabine O. 

[AlB]

@SabineOussi 

Help me understand what you exactly mean by:

What if a condition has to be made on one of the measure showing only a certain threshold? Let's say measure1 <=3%

Please show the expected result for this case (at least for a couple of lines) with an explanation. What needs to be shown in the result, what not...

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[SabineOussi]

In my sample file, I want to only show values with measure1<=0.03

Expected result would be:

Area1 | Section1 (C 2% [4], D 1% [3])

Area1 | Section3 (G 2% [2])

Area2 | Section1 (B 2% [1], C 3% [3])

Area2 | Section4 (C 2% [6], D 3% [5])

Area2 | Section5 (K 1% [2])

[SabineOussi]

@AlB What if a condition has to be made on one of the measure showing only a certain threshold?
Let's say measure1 <=3%

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[AlB · Accepted solution]

Hi @SabineOussi 

What would the expected result be?

Try this

CONCAT =
CONCATENATEX (
    VALUES ( 'Table'[Column1] ),
    'Table'[Column1] & " ("
        & CALCULATE (
            CONCATENATEX (
                VALUES ( 'Table'[Column2] ),
                'Table'[Column2] & " "
                    & FORMAT ( [Measure1], "0%" ) & " [" & [Measure2] & "]",
                ", ",
                'Table'[Column2], ASC
            )
        ) & ")",
    "
"
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[SabineOussi]

Thank you @AlB 
It is working now.