concatenate of multiple column in a single column

[Anonymous]

Hi All,

  I am trying to concatenate 3 column values (UserName,Request Sent and Response received) where one is Text and other two are integers as per the snap below. 

This data I am using as a source to drill down chart where on click on each squad I need UserName with sum of Request sent and Response Received.

Expected Output:

On click of CFO Legal(Squad) -> Danny (1/1)

                                                     Chris (0/1)

                                                     Vikram (0/1)

                                                     Kally (1/1)

On click of CFO Office(Tribe) -> Danny (2/2)

                                                   Chris (1/2)

                                                   Vikram (1/2)

                                                   Kally (1/2)

Any help would be much appriciated.

Thanks,

Sud

[johnmu]

Is there a reason the output needs to be in a single field?

Could you not make a table with Username, Responses Received, and Requests Sent as the field with Squad and Tribe set as filter visualizations?

You would get the expected output, in seperate fields

[Anonymous]

Hi @johnmu,

  This is the business requirement. I need a single column value for this to use in chicletSlicer where I can't use more than one column.

Thanks,

Sud 

[johnmu]

Hmm...I have a feeling that's not possible, since I think the only way to achieve something like that would be with a measure, and a measure can't be used as a filter. 

[Anonymous]

With a column I tried without SUM it works. When I add SUM to Request sent and Response Received it gives the sum of all instead of grouping. Here is the DAX without SUM.

Column1 = CONCATENATE( Table1[UserName] & "(" & Table1[RequestSent], Table1[Response Received] & ")" )

Thanks,

Sud

[v-yuta-msft · Accepted solution]

Hi Sud,

Power bi doesn't support creating dynamic column using selection value of a slicer and you also can't select column name from a slicer because slicer only filter rows. In addtion, it seems like you just want to calculate the aggregation value by two columns [Tribe] and [Squad], as a workaround, you can create two calculate columns using DAX formula below:

Based on Tribe = 
VAR Total_Of_Request = CALCULATE(SUM(Table1[Request Sent]), ALLEXCEPT(Table1, Table1[UserName]))
VAR Total_Of_Response = CALCULATE(SUM(Table1[Response Received]), ALLEXCEPT(Table1, Table1[UserName]))
RETURN
Table1[UserName] & " " & "(" & Total_Of_Response & "/" & Total_Of_Request & ")"

Based on Squad = 
VAR Total_Of_Request = CALCULATE(SUM(Table1[Request Sent]), FILTER(ALLEXCEPT(Table1, Table1[UserName]), Table1[Squad] = "CFO Legal"))
VAR Total_Of_Response = CALCULATE(SUM(Table1[Response Received]), FILTER(ALLEXCEPT(Table1, Table1[UserName]), Table1[Squad] = "CFO Legal"))
RETURN
Table1[UserName] & " " & "(" & Total_Of_Response & "/" & Total_Of_Request & ")"

  

Hope it's helpful to you.

Jimmy Tao