cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sud Regular Visitor
Regular Visitor

concatenate of multiple column in a single column

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. 

 

Capture.PNG

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: concatenate of multiple column in a single column

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 & ")"

1.PNG2.PNG  

 

Hope it's helpful to you.

Jimmy Tao

5 REPLIES 5
johnmu Frequent Visitor
Frequent Visitor

Re: concatenate of multiple column in a single column

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

Sud Regular Visitor
Regular Visitor

Re: concatenate of multiple column in a single column

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 Frequent Visitor
Frequent Visitor

Re: concatenate of multiple column in a single column

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. 

Sud Regular Visitor
Regular Visitor

Re: concatenate of multiple column in a single column

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

Community Support Team
Community Support Team

Re: concatenate of multiple column in a single column

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 & ")"

1.PNG2.PNG  

 

Hope it's helpful to you.

Jimmy Tao