Forum Discussion

plantew1's avatar
plantew1
Icon for Helper I rankHelper I
2 years ago
Solved

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...
Need Help
  • TheoC's avatar
    TheoC
    2 years ago

    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

     

TheoC's avatar
TheoC
Icon for Community Champion rankCommunity Champion
2 years ago

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

 

plantew1's avatar
plantew1
Icon for Helper I rankHelper I
2 years ago

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's avatar
    plantew1
    Icon for Helper I rankHelper I
    2 years ago

    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.