MEASURE: concatenate multiple columns based on slicer value(s)

[eriksjoo]

Hello first time poster here. Been searching for a solution to my problem for several hours and was hoping you could help me.

I have a situation where I want the user to be able to select which values to display in a matrix visual, by selecting value(s) in a slicer and getting the result in 1 box in the matrix.

I have an EmployeeTable that has all the data and looks like this:

Employee	Week	Weekday	Work Order	Comment	Function
Employee A	43	Monday	331133	XY	Driver
Employee A	43	Wednesday	331113	TYYY	Admin
Employee A	43	Thursday	444133	XLEA	Operator
Employee B	43	Tuesday	419033	POE	Driver
Employee B	43	Wednesday	321031	FLE	Driver
Employee F	43	Friday	302131	FAE	Admin
Employee F	43	Saturday	493019	MOE	Operator

I have a SlicerTable that looks like this:

Info

Work Order

Comment

Function

I want the user to select any combination from these three values and getting the result in a matrix visual.

If the user selects Work Order they get this result:

Employee	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
Employee A	331133		331113	444133
Employee B		419033	321031
Employee F					302131	493019

If they select Work Order and Comment they get this result:

Employee	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
Employee A	331133 XY		331113 TYYY	444133 XLEA
Employee B		419033 POE	321031 FLE
Employee F					302131 FAE	493019 MOE

If the user selects Comment and Function they should get this result:

Employee	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
Employee A	XY Driver		TYYY Admin	XLEA Operator
Employee B		POE Driver	FLE Driver
Employee F					FAE Admin	MOE Operator

I've started writing some DAX expressions, starting out with 1 or 2 of the columns.

I have so far:

Measure = 
IF(COUNTAX(FILTER(SlicerTable;SlicerTable[Info]="Work Order");SlicerTable[Info])>0;

    CONCATENATEX(

        VALUES(EmployeeTable[Work Order]);

        EmployeeTable[Work Order];

        ", "

   );

   ""

)

Measure2=

CALCULATE(

IF(COUNTAX(FILTER(SlicerTable;SlicerTable[Info]="Work Order");SlicerTable[Info])>0;

    MAX(EmployeeTable[Work Order]);

    "")&UNICHAR(10)&

IF(COUNTAX(FILTER(SlicerTable;SlicerTable[Info]="Work Order");SlicerTable[Info])>0;

    MAX(EmployeeTable[Comment])

    ;"")

)

Both of these measures seem to yield the same result, i.e. that they show the value regardless of the outcome of the IF. So for Measure i get the matrix with the Work Order values in every cell. For Measure 2 I get Work Order and Comment in every cell.

HOWEVER, when I create a card visual and select a cell in the matrix visual, the card displays the correct result. 

Does anyone have any insight into either how to write a dax expression, or enlighten me as to why the card visual displays correct information when I click on a cell in the matrix, but the matrix itself does not?

Appreciative for any support

[amitchandak · Accepted solution]

@eriksjoo , Create a measure like this and use

measure =
var _1 = countx(filter(SlicerTable, SlicerTable [Value] ="Work Order"),SlicerTable [Value])+0
var _2 = countx(filter(SlicerTable, SlicerTable [Value] ="Comment"),SlicerTable [Value])+0
var _3 = countx(filter(SlicerTable, SlicerTable [Value] ="Function"),SlicerTable [Value])+0

return
if(_1 >0 , max(employee[Work Order]),"") & " " & if(_2 >0 , max(employee[Comment]),"") & " " & if(_3 >0 , max(employee[Function]),"")

[wdx223_Daniel · Accepted solution]

@eriksjoo 

=var _s=allselected(slicer[column]) return concatenatex(table,if("work order" in _s,table[work order])&if("comment" in _s,table[comment])&if("function" in _s,table[function]),",")

[eriksjoo]

Thank you very much Daniel! This also worked 

[amitchandak · Accepted solution]

@eriksjoo , Create a measure like this and use

measure =
var _1 = countx(filter(SlicerTable, SlicerTable [Value] ="Work Order"),SlicerTable [Value])+0
var _2 = countx(filter(SlicerTable, SlicerTable [Value] ="Comment"),SlicerTable [Value])+0
var _3 = countx(filter(SlicerTable, SlicerTable [Value] ="Function"),SlicerTable [Value])+0

return
if(_1 >0 , max(employee[Work Order]),"") & " " & if(_2 >0 , max(employee[Comment]),"") & " " & if(_3 >0 , max(employee[Function]),"")

[eriksjoo]

Thank you so much! This worked brilliantly! Saved my night 🙂