Forum Discussion
Helper IHow to extract results based on selection
Greetings!
As see form the numerous posts there is a mixture of 'neewbies' and experienced datanauts here so I thought I would post something that I am struggling to get to grips with!
I have two data sets as follows;
- List of Employees showing 'codes' and 'levels' for the codes (1 or 2 assignment category)
- List of Services based upon the 'codes' needed to demonstrate 'Authority' to provide Service (Codes are needed to be held at Level 2)
I would like to be able to produce a report that uses a slicer to show the service and the codes for the service (simple), but then interrogate the employee list to see who is authorised for that selected service. I appreciate there is no relationship between the two tables and I may need to use the USEREALTIONSHIP for this but I am unsure the approach not the basis of filtering / selecting the results based upon the selection. Would I need to consider a calculated table / measure or other form of manipulation?
As you may guess I am on the exponential curve of learning with PowerBI!! I can achieve what I want in Excel with ARRAYS, INDEX, MATCH, INDIRECT but any assistance on best approach to take would be fantastic!!
Service001 - ResultService002 - ResultService
Employees
hi, DavidH
I suggest you try this data model:
1. Create a Employee Name fact table then create a relationship with Employees table
2. Use this formula to create a measure
Measure = var Scoreneeded=CALCULATE(MAXX('Employee Name',CALCULATE(SUM(Employee[Level]))),ALLSELECTED('Employee Name')) return IF(Scoreneeded=CALCULATE(MAXX('Employee Name',CALCULATE(SUM(Employee[Level])))), "Authorised" , "Not Authorised")Result:
here is sample pbix file, please try it.
Best Regards,
Lin
5 Replies
v-lili6-msftCommunity Support
hi, DavidH
You could try this way:
In Edit Queries, Select Code1, Code2, Code3, Code4 then Unpivot them,
Now you could create the relationship with Employees table and use Service for slicer.
Best Regards,
Lin
DavidHLin,
Many thanks for your comments!
Having followed your suggestion I have the information arranged now with a new table (Reference) with the unpivoted coluimns. I have created the relationship between the tables now as follows;
Service - Service (Unpivot as Reference) 1:Many
Service (Unpivot as Reference) - Employees Many:Many
Just wondering if this is the correct way to approach this? However, with the report view I have also created a measure to determine the 'Score needed' based on the slicer selection which works fine.
I can display the Employees on a Matrix visual which shows what they results are....but what I would like to now do is physically show on the matrix who is 'Authorised' and who is 'Not Authorised'.
Is there any way I can use the measure or include something in the Matrix to show this?
- v-lili6-msft
hi, DavidH
I suggest you try this data model:
1. Create a Employee Name fact table then create a relationship with Employees table
2. Use this formula to create a measure
Measure = var Scoreneeded=CALCULATE(MAXX('Employee Name',CALCULATE(SUM(Employee[Level]))),ALLSELECTED('Employee Name')) return IF(Scoreneeded=CALCULATE(MAXX('Employee Name',CALCULATE(SUM(Employee[Level])))), "Authorised" , "Not Authorised")Result:
here is sample pbix file, please try it.
Best Regards,
Lin
- DavidH
Dear Lin,
Fantastic just what I needed!
Many thanks for your support over this query and solution......I have mnarked this as solved!
Kind regards
DavidH
- DavidH
A follow on from the previous post which was kindly assisted by Lin.
Is there any way I can take the results of the measure to display this on a map visual where the the visual will show the bubble with the split of who is authorised or not?
So for example if we assume all the employees are based in, say, London - UK is it possible for this to visualise? I have been trying to grasp the ways in which I can but so far not able to do so....