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 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.

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 

     

10 Replies

  • AlB's avatar
    AlB
    Community Champion
    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
    Community Champion
    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

      Perfect.

      It returns exactly what I want.

       

      Thanks a lot!
      Sabine O.

  • SabineOussi's avatar
    SabineOussi
    Skilled Sharer
    5 years ago

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

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    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'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