Forum Discussion
Segregation of data based on multiple tables
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
Well, you will want to unpivot the Code# columns in that first table.
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
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