Is there a way to make CONCATENATEX dynamic in a measure?

[peterhui50]

I have this table here. I have created a calculated column and it meets our needs to some extent but fails when users want to slice.

the path column is a calculated column so if users slice by the top 2 entires of NAME A, it will still give all the concatenated entries.

It really should just be a measure and concatenate by Level 1 A -> Level 2 A when user selects the first two entries for NAME A

I tried several things first, instead of a calculated column, I used a measure, but to create the measure, it seems like this fails.

Measure = CONCATENATEX(ALLEXCEPT('Table (4)','Table (4)'[NAME]),'Table (4)'[Job],"=>")

it starts giving me ALL of the job entries. I thought ALLEXCEPT would provide a grouping based on NAME column here? Not sure what I am doing wrong. Why would it do that a column but not in a measure??

Any help would be greatly appreciated.

[v-henryk-mstf · Accepted solution]

Hi @peterhui50 ,

May wish to create this formula in the measure, by filtering the Data field in the "Filter", you can see the dynamic change of the value of the path in the card.

Path = 
CALCULATE (
    CONCATENATEX ( DISTINCT ( 'Table'[Job] ), [Job], ">>" ),
    ALLSELECTED ( 'Table'[Date] )
)

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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

[peterhui50]

Thanks, I had one other grouping and using this formula, I added

Measure 2 =

CALCULATE (

CONCATENATEX ( DISTINCT ( 'Table (4)'[Job] ), [Job], ">>" ),

ALLSELECTED ( 'Table (4)'[Action_Type] ),ALLEXCEPT( 'Table (4)', 'Table (4)'[NAME],'Table (4)'[Action_Type])

)

now the path is dynmaic based on the action type . Thank you for your solution! 

[peterhui50]

Thanks for your response.

I tried the measure but it still doesn't do what I wanted to do. When I filtered by two dates, the measure still concats the whole column instead of the jobs that are within the specific range. I'm wodering if ALLSELECTED needs to be in play here? 

[amitchandak]

@peterhui50 , Try like

Measure = calculate(CONCATENATEX(distinct('Table (4)'[Job]), [Job],"=>"), ALLEXCEPT('Table (4)','Table (4)'[NAME]))