Segregation of data based on multiple tables

[DavidH]

I am trying to look at generating a model that displays information about who in the organisation can deliver products based on their skills.

So if you select an employee this will show all the products and services that they are able to deliver. The attached picture hopefully captures sufficinet information to get the problems across.

I have a feeling that some measure branching of virtual tables may be needed??

Any suggestions appreciated!

Regards,

DavidH

Data example

[Greg_Deckler]

Well, you will want to unpivot the Code# columns in that first table.

[DavidH]

Greg,

Forgive me I should have been specific about the initial setting up of the data. Obviously simple data transformations will need to be completed before committing the data in to the model.

Basic DAX to determine how many codes for a product exist but its the segregation and and ability to capture what an employee is able to deliver which is the main question.

DavidH

[DavidH · Accepted solution]

Ok I have managed to solve this one!

I created a distinct list of Product Codes and then used this for the relationship between Product and Empoyees.

Created a measure to determine the 'score' needed 

Product Score = CALCULATE( COUNTROWS( 'Product Codes' ) ) * 2

Created a measure to calculate the employee score for each Product

Employee Check Y/N = IF( ISFILTERED( 'Employee Authorisations'[Employees] ),

                        IF( CALCULATE( SUMX( 'Employee Authorisations', 'Employee Authorisations'[Level] ),

                        TREATAS( VALUES( 'Product Codes'[Code ID] ), 'Employee Authorisations'[Code ID] ) ) = [Product Score],

                        "Authorised", BLANK() ),

                     BLANK() )

If there are any suggested alternate solutuions I would be interested to know!

Regards,

DavidH