Forum Discussion
Power BI - 2 Columns with similar values
Hi
I am pretty new to Power BI. Have a very basic question. I am preparing a roster report on a calendar matrix view based on excel data. Now the concern is I have 2 columns Task 1 and Task 2 having the same set of values against each employee. Task 1 for one employee could be Task 2 for the other and vice versa.
My requirement is basically I should be able to slice the data based on both the values. For isntance is I select Call Handling, I should get count 3 since based on the combination of Task 1 or Task 2. How will I achieve this in PowerBI. Thanks for your support
I dont want to combine the 2 columns and create a merged cell, because that will not solve my purpose
Task 1
Call Handling
Incidents
Mailbox
Escalations
Task 2
Issues
Access
Escalations
Access
Regards
Febin Gafoor
Hi febing
Not sure if I understand the target correctly. You can add an additional table to have all distinct task values in a column. Lets call the table 'Dim Task'. Make sure this table is disconnected from other tables. Use task column from this 'Dim Task' table into the slicer. Then you can try the following measure to count the results.
count measure = VAR _selectedTask = SELECTEDVALUE ( 'Dim Task'[Task] ) RETURN COUNTROWS ( FILTER ( 'table', 'table'[Task 1] = _selectedTask ) ) + COUNTROWS ( FILTER ( 'table', 'table'[Task 2] = _selectedTask ) )Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
3 Replies
- lbendlin
Super User
Please read about data models, dimensions and facts, Prepare your data accordingly and then wire it up in Power BI according to the business question.
Hi febing
Not sure if I understand the target correctly. You can add an additional table to have all distinct task values in a column. Lets call the table 'Dim Task'. Make sure this table is disconnected from other tables. Use task column from this 'Dim Task' table into the slicer. Then you can try the following measure to count the results.
count measure = VAR _selectedTask = SELECTEDVALUE ( 'Dim Task'[Task] ) RETURN COUNTROWS ( FILTER ( 'table', 'table'[Task 1] = _selectedTask ) ) + COUNTROWS ( FILTER ( 'table', 'table'[Task 2] = _selectedTask ) )Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!Perfect my friend. This is exactly what i was looking for. Infact i was joining all the tables and this was not giving the correct result. Thanks once again.
