DAX compute Conditional Status giving custom rules

[Atf23]

Hi everyone, 
I need some help with this sample. I have 2 tables: a config table that contains the quiz list categorized by level and category and wether the quiz is required and how the trainee can validate a category and then a level.
For example to validate the category Aa, a trainee must succeed the quiz 1 OR 2, but to succeed the cataegory Ab, a trainee must succeed the quizes 3 and 4. To validate the level A, a trainee should validate all the categories within that level.
In the second table, we find the results of the quiz with 3 possible status complete (success), incomplete(not yet finished) and failed. 
what i need to visualize is a matrix where trainees in rows and in columns levels and categories and as values a computed status that shows if the trainee suceeded each category and level. if not, display incomplete or failed given the results
here is a sample of data. Thanks a lot for you help !

id_quiz level category required operator
1 A Aa 1 OR
2 A Aa 1 OR
3 A Ab 1 AND
4 A Ab 1 AND
5 B Ba 1 AND
6 B Bb 1 AND

id_trainee id_quiz status date
101 1 complete 2023-01-01
101 3 complete 2023-01-03
101 4 complete 2023-01-03
101 5 complete 2023-01-03
101 6 complete 2023-01-03
102 2 complete 2023-01-02
102 3 complete 2023-01-05
102 4 complete 2023-01-05
103 1 complete 2023-01-04
103 3 complete 2023-01-04
103 4 failed 2023-01-04
104 1 incomplete 2023-01-04

[Anonymous]

Hi @Atf23,

Glad your issue has been resolved!
I suggest you to accept your super user post as the solution — it will help other community members facing similar problems to find the answer faster.

Regards,

Vinay

[Atf23]

Hi, thanks a lot for your answer, that inspired me. I compute this measure. It works fine by category and trainee. Now I need to check wether all categories within some level are succeeded. Thanks a lot !

CategoryStatus =

// selected values

var lev = SELECTEDVALUE(Config[level])

var cat = SELECTEDVALUE(Config[category])

var trainee = SELECTEDVALUE(Results[id_trainee])

// computed values and tables

var op = CALCULATE(MAX('Config'[operator]);FILTER(

        'Config';

        'Config'[category] = cat &&

        'Config'[level] = lev  &&

        'Config'[required] = 1  

        ))  

VAR required = CALCULATETABLE(VALUES('Config'[id_quiz]); FILTER(

        'Config';

        'Config'[category] = cat &&

        'Config'[level] = lev  &&

        'Config'[required] = 1  

        ))

var completeQuiz = CALCULATETABLE(VALUES('Results'[id_quiz]);FILTER(

        'Results';

        'Results'[id_trainee] = trainee &&

        'Results'[status] = "complete"

        ))  

var incompleteQuiz = CALCULATETABLE(VALUES('Results'[id_quiz]);FILTER(

        'Results';

        'Results'[id_trainee] = trainee &&

        'Results'[status] = "incomplete"

        ))

var failedQuiz = CALCULATETABLE(VALUES('Results'[id_quiz]);FILTER(

        'Results';

        'Results'[id_trainee] = trainee &&

        'Results'[status] = "failed"

        ))

// intersections        

var interComplete = INTERSECT(completeQuiz; required)

var interIncomplete = INTERSECT(incompleteQuiz; required)

var interFailed = INTERSECT(failedQuiz; required)

// copute rows

var crReq = COUNTROWS(required)

var crInterComplete = COUNTROWS(interComplete)

var crInterIncomplete = COUNTROWS(interIncomplete)

var crInterFailed = COUNTROWS(interFailed)

// status

RETURN

    SWITCH(

        true();

        op = "OR" && crInterComplete = 0 && crInterIncomplete = 0  && crInterFailed > 0; "failed" ;

        op = "OR" && crInterComplete = 0 && crInterIncomplete > 0; "incomplete" ;

        op = "OR" && crInterComplete > 0; "passed" ;

        op = "AND" && crInterComplete = crReq; "passed" ;

        op = "AND" && crInterFailed > 0; "failed" ;

        op = "AND" && crInterFailed = 0 && crInterIncomplete > 0; "incomplete" ;

        "-"

    )

[Atf23]

Hi everyone, 
I am always looking for better approach. Thanks a lot !

[bhanu_gautam · Accepted solution]

@Atf23 Create a Calculated Column for Category Status

dax
CategoryStatus =
VAR TraineeID = 'Results'[id_trainee]
VAR Category = 'Config'[category]
VAR Level = 'Config'[level]
VAR RequiredQuizzes =
FILTER(
'Config',
'Config'[category] = Category && 'Config'[level] = Level
)
VAR CompletedQuizzes =
FILTER(
'Results',
'Results'[id_trainee] = TraineeID && 'Results'[status] = "complete"
)
VAR RequiredCount = COUNTROWS(RequiredQuizzes)
VAR CompletedCount = COUNTROWS(
INTERSECT(RequiredQuizzes, CompletedQuizzes)
)
VAR Operator = MAXX(RequiredQuizzes, 'Config'[operator])
RETURN
IF(
Operator = "AND",
IF(CompletedCount = RequiredCount, "complete", "incomplete"),
IF(CompletedCount > 0, "complete", "incomplete")
)

Create a Calculated Column for Level Status:

dax
LevelStatus =
VAR TraineeID = 'Results'[id_trainee]
VAR Level = 'Config'[level]
VAR Categories =
DISTINCT(
SELECTCOLUMNS(
FILTER('Config', 'Config'[level] = Level),
"Category", 'Config'[category]
)
)
VAR CategoryStatuses =
ADDCOLUMNS(
Categories,
"Status",
CALCULATE(
MAX('Results'[CategoryStatus]),
FILTER('Results', 'Results'[id_trainee] = TraineeID)
)
)
VAR IncompleteCount = COUNTROWS(
FILTER(CategoryStatuses, [Status] = "incomplete")
)
VAR FailedCount = COUNTROWS(
FILTER(CategoryStatuses, [Status] = "failed")
)
RETURN
IF(
IncompleteCount > 0, "incomplete",
IF(FailedCount > 0, "failed", "complete")
)

Add a Matrix visual to your Power BI report.
Place id_trainee in the Rows.
Place level and category in the Columns.
Use the CategoryStatus and LevelStatus calculated columns as Values.