Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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...
Need Help
Tips and Tricks
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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" ;
        "-"
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

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