Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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
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.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |