Forum Discussion

Sudbrl's avatar
Sudbrl
Frequent Visitor
3 years ago
Solved

CONCATENATEX with count

Dear ALL,

 

For a report, I want to achieve concat with the count. Please provide a solution on DAX measure for power pivot.

Result/Output

NameRegionCount
AnilA1(2),A2(2)
SunilA1(2),A2(2)

 

Greg_Deckler 

 

 

Table

NameRegion
AnilA1
AnilA1
AnilA2
AnilA2
SunilA1
SunilA1
SunilA2
SunilA2
  • smpa01's avatar
    smpa01
    3 years ago

    Sudbrl  you can do this

    Measure 2 = 
CONCATENATEX (
    ADDCOLUMNS (
        SUMMARIZE ( 'fact', 'fact'[Name], 'fact'[Region] ),
        "result",
            [Region] & "," & "("
                & CALCULATE (
                    DISTINCTCOUNT ( 'fact'[Region] ),
                    ALLEXCEPT ( 'fact', 'fact'[Name] )
                ) & ")"
    ),
    [result],
    ",",
    [Name]
)

     

     

1 Reply

  • smpa01's avatar
    smpa01
    Community Champion
    3 years ago

    Sudbrl  you can do this

    Measure 2 = 
CONCATENATEX (
    ADDCOLUMNS (
        SUMMARIZE ( 'fact', 'fact'[Name], 'fact'[Region] ),
        "result",
            [Region] & "," & "("
                & CALCULATE (
                    DISTINCTCOUNT ( 'fact'[Region] ),
                    ALLEXCEPT ( 'fact', 'fact'[Name] )
                ) & ")"
    ),
    [result],
    ",",
    [Name]
)