Forum Discussion

SabineOussi's avatar
SabineOussi
Skilled Sharer
5 years ago
Solved

CONCATENATEX Empty Records

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...
Need Help
Tips and Tricks
  • AlB's avatar
    AlB
    5 years ago

    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's avatar
    AlB
    5 years ago

    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's avatar
SabineOussi
Skilled Sharer
5 years ago

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's avatar
AlB
Community Champion
5 years ago

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's avatar
    SabineOussi
    Skilled Sharer
    5 years ago

    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])