Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
plantew1
Helper I
Helper I

Concatenate on Duplicate

Hello everyone.

I have written the measure shown below. I get the following duplicated Rank values. What I would like to do is concatenate the CIS Type Description value when a Rank is duplicated. I have tried a few measures that don't seem to work. Is there anyone who might be able to point me in the right direction? Thanks.

plantew1_0-1705420112039.png

 


CIS - Rank CIS Types Current Month =
VAR _Calc=
RANKX(
ALL( 'Lookup: CI Types'[CIS Type ID] ),
SUMX(
FILTER(
ALL('Lookup: CI Types'[CIS Type Description]),
NOT('Lookup: CI Types'[CIS Type Description] ) in { "Criteria One", "Criteria Two" }
),
[CIS - Intake Count by CI Type-Subtype Current Month]
),
, DESC, Skip
)
RETURN
_Calc​

1 ACCEPTED SOLUTION
TheoC
Super User
Super User

Hi @plantew1 

 

Can you try the below? 

 

 

CIS - Rank CIS Types with Concatenation =
VAR _Ranking = 
SUMMARIZE(
    ALL ( 'Lookup: CI Types' ) ,
    'Lookup: CI Types'[CIS Type ID] ,
    "Rank" , RANKX (
        ALL ( 'Lookup: CI Types'[CIS Type ID] ) ,
        SUMX (
            FILTER (
                ALL ( 'Lookup: CI Types'[CIS Type Description] ) ,
                NOT ( 'Lookup: CI Types'[CIS Type Description] ) IN { "Criteria One", "Criteria Two" }
            ) ,
            [CIS - Intake Count by CI Type-Subtype Current Month]
        ) ,
        , DESC , Skip
    ) ,
    "TypeDesc" , 'Lookup: CI Types'[CIS Type Description]
)

VAR _ConcatenateTypes = 
CALCULATE (
    CONCATENATEX (
        FILTER (
            _Ranking ,
            [Rank] = EARLIER ( [Rank] )
        ) ,
        [TypeDesc] , ", " ) , 
        REMOVEFILTERS ( 'Lookup: CI Types'[CIS Type ID] )
)

RETURN
IF (
    COUNTROWS (
        FILTER (
            _Ranking ,
            [Rank] = EARLIER ( [Rank] )
        )
    ) > 1 ,
    _ConcatenateTypes ,
    MAX ( 'Lookup: CI Types'[CIS Type Description] )
)

 

Hope this works... let me know how it goes.

 

Theo

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

View solution in original post

4 REPLIES 4
TheoC
Super User
Super User

@plantew1 that's great to hear that it worked mate! Well done!

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

TheoC
Super User
Super User

Hi @plantew1 

 

Can you try the below? 

 

 

CIS - Rank CIS Types with Concatenation =
VAR _Ranking = 
SUMMARIZE(
    ALL ( 'Lookup: CI Types' ) ,
    'Lookup: CI Types'[CIS Type ID] ,
    "Rank" , RANKX (
        ALL ( 'Lookup: CI Types'[CIS Type ID] ) ,
        SUMX (
            FILTER (
                ALL ( 'Lookup: CI Types'[CIS Type Description] ) ,
                NOT ( 'Lookup: CI Types'[CIS Type Description] ) IN { "Criteria One", "Criteria Two" }
            ) ,
            [CIS - Intake Count by CI Type-Subtype Current Month]
        ) ,
        , DESC , Skip
    ) ,
    "TypeDesc" , 'Lookup: CI Types'[CIS Type Description]
)

VAR _ConcatenateTypes = 
CALCULATE (
    CONCATENATEX (
        FILTER (
            _Ranking ,
            [Rank] = EARLIER ( [Rank] )
        ) ,
        [TypeDesc] , ", " ) , 
        REMOVEFILTERS ( 'Lookup: CI Types'[CIS Type ID] )
)

RETURN
IF (
    COUNTROWS (
        FILTER (
            _Ranking ,
            [Rank] = EARLIER ( [Rank] )
        )
    ) > 1 ,
    _ConcatenateTypes ,
    MAX ( 'Lookup: CI Types'[CIS Type Description] )
)

 

Hope this works... let me know how it goes.

 

Theo

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Hi TheoC,

First, just wanted to thank you for taking the time to code this for me. I'm truely greatful.

I think you have managed to come extremely close.

The DAX intellisense was giving me issues (an error) on this line and the only way to get it to cooperate was to switch the CI Type Description field for the CI Type ID field. Unfortunately, this concatenates the ID field instead of the description field. I've tried hard to understand your method and am stuck to figure out how to modify it so that it's concatenating the descriptions instead of the IDs but doesn't produce an error on this line. I'm still learning, I'm afraid.

    ) ,
    "TypeDesc" , 'Lookup: CI Types'[CIS Type Description]
)

 

plantew1_0-1705455384047.png

 

Actually, TheoC, I just figured it out. I needed to wrap the description field in MAX and now it works perfectly. Thank you so much for your time and for your help in finding a soluation.

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.