Forum Discussion
DAX compute Conditional Status giving custom rules
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 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.
4 Replies
Anonymous 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.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 valuesvar lev = SELECTEDVALUE(Config[level])var cat = SELECTEDVALUE(Config[category])var trainee = SELECTEDVALUE(Results[id_trainee])// computed values and tablesvar 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"))// intersectionsvar interComplete = INTERSECT(completeQuiz; required)var interIncomplete = INTERSECT(incompleteQuiz; required)var interFailed = INTERSECT(failedQuiz; required)// copute rowsvar crReq = COUNTROWS(required)var crInterComplete = COUNTROWS(interComplete)var crInterIncomplete = COUNTROWS(interIncomplete)var crInterFailed = COUNTROWS(interFailed)// statusRETURNSWITCH(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" ;"-")