Concatenate values in a single cell of a table visual

[max4061]

Objective: To get the result of the test measure in a single cell for the account

Sample Data: 
was not able to paste in the table format due to some error

GLE Parent Conf Code Actuals YTD PY Actuals YTD TEstttt
SALESFORCE APN38N 305 151.2
SALESFORCE BBIL12N 53.49681620541142

SALESFORCE BI18AN 61.656262722214684 74.22988193784774
SALESFORCE BI22N 390.4 325.6
SALESFORCE BIE25IN
242.67874903304232 BIE24IN
SALESFORCE BII12N 43.10981530799627 37.255674880541484
SALESFORCE CSMUS24N 232.1 218
SALESFORCE ESC36N
697.7566581942757 ESC35N
SALESFORCE GML10N 231.4 203
SALESFORCE INDIA19N
105.41071377065889 INDIA18N
SALESFORCE PCC24N 118.3 105
SALESFORCE PS32N
171.2579725878681 PS31N
SALESFORCE SYM34N 830.476 968.2

Desirec output: 

GLE Parent Actuals YTD PY Actuals YTD TEstttt
SALESFORCE 2265.9388942356222 3299.5896504042344 ESC35N,BIE24IN, INDIA18N, PS31N
Tables: Events and Dim Opty
Events filters one to many Dim Opty basis conf code

Details: Actuals YTD and PY Actuals YTD are current year and previous year revenues. Test measure computes the non retained conferences but without conf code in picture test measures return blanks. I have tried different solutions but nothing works.

Test Measure =

Var CY = CALCULATE(SUMX(Distinct(Events[Conf Code]),
[Current Actuals USD - CY Peg Rate]))
Var PY = CALCULATE(SUMX(Distinct(Events[Conf Code]),[GLE PY Actuals Same Days Out LY - USD CY Peg Rate]))
Var Result =
SWITCH(
    TRUE,
    CY > 0 && PY > 0,  -- Retained account (revenue in both years)
    0,
    PY > 0 && CY = 0,   -- Non-retained account (revenue last year only)
    1,
    BLANK()  -- Other cases (no revenue in either year)
)

VAR __NonRetainedCodes =
    FILTER(DISTINCT(Events[PY Conf Code New]), Result = 1)
    --PY > 0 && (ISBLANK(CY) = TRUE() || CY = 0))
Return

    CONCATENATEX(
      __NonRetainedCodes,
      Events[PY Conf Code New],
      ", "  -- You can change the delimiter here (e.g., ";")
    )

Solutions Tried: I have tried creating virtual table within dax measure to get the result but it doesn't work. I do not want to create a table and get the result as in future requirement may get complex so want to be dependent on the measure. 

Kindly hellp
#community #powerbi #daxhelp

[Shosher]

Without the dataset i can't really test intrecate DAX, but you're on the right path. 

ConcatenateX will do what you require, Concetenate all returned values. Your table as you have it will already filter much data for you. Try making it simpler first. 

Remove all filter statements and the switch case (save them somewhere ofcourse). And try to just get that concatenateX to work on the base table events. Place it in your visual to get all py conf codes to show up regardless if other measures have results or not. This should make your table too big, but get your results as you want them. Try filtering after that. You can always try evaluating the concatX in a variable and creating a simple IF statement at the return line checking your results and forcing a blank value on rows where it should be empty and returning the concat var where it shouldnt. Not the prettiest of solutions but it will work instead of trying to use a filter statement in your concat which is throwing an error somewhere. 

[max4061]

@amitchandak can you please help.

[v-xinruzhu-msft · Accepted solution]

Hi,

Thanks for the solution @Shosher  provided, and i want to offer some more information for uer to refer to.

hello @max4061 , based on your description, you can split the measue to two measures.

Test Measure =

VAR CY =
    CALCULATE (
        SUMX ( DISTINCT ( Events[Conf Code] ), [Current Actuals USD - CY Peg Rate] )
    )
VAR PY =
    CALCULATE (
        SUMX (
            DISTINCT ( Events[Conf Code] ),
            [GLE PY Actuals Same Days Out LY - USD CY Peg Rate]
        )
    )
RETURN
    SWITCH (
        TRUE,
        CY > 0
            && PY > 0, 
        0,
        PY > 0
            && CY = 0, )
        1,
        BLANK () 
    )

Result =
VAR __NonRetainedCodes =
    CALCULATETABLE (
        DISTINCT ( Events[PY Conf Code New] ),
        FILTER ( ALLSELECTED ( Event ), [Test Measure] = 1 )
    )
RETURN
    CONCATENATEX ( __NonRetainedCodes, [PY Conf Code New], ", " )

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.