Forum Discussion

afaherty's avatar
afaherty
Helper V
2 years ago
Solved

Insert Column Help

Greetings all,

I would like to insert a column to categorize students as "pass" or "fail."  But the key here is that they can take their test in any subject multiple times.  So if they've failed any test 4 times but passed once, then they count as a pass.  I want to be able to see this in a column (the "Category" column below).  Can anyone help?

 

Example data:

StudentIDDateTestPassFailCategory
11/1/24MathFailPass
12/1/24MathFailPass
13/1/24EnglishPassPass
23/12/24WritingPassPass
24/12/24MathFailPass
35/1/24EnglishFailFail
34/2/24WritingFailFail
41/2/24MathFailPass
42/2/24EnglishPassPass
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, afaherty 

    Based on your description and the data information you provided, I used the following sample data:

    I created a calculated column using the following DAX expression:

    Column = 
VAR _table = FILTER(ALL('Table'),'Table'[StudentID]=EARLIER('Table'[StudentID]))
RETURN IF("pass" IN SELECTCOLUMNS(_table,"aa",'Table'[PassFail]),"Pass","Fail")

    Here are the results:

    I've uploaded the PBIX file I used this time below.

     

     

    How to Get Your Question Answered Quickly

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

    Best Regards

    Jianpeng Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

  • some_bih's avatar
    some_bih
    2 years ago

    Hi afaherty one possible solution is to create measure Measure_Fail and create new column, like Measure_Catagory with reference to that measure (create measure first) and you should get output

    Measure_Catagory = [Measure_Fail]

     

    Measure_Fail =
    VAR __check_fail_or_pass =
    CALCULATE(
        DISTINCTCOUNT(Table_1[PassFail]),
        Table_1[PassFail] = "Pass",
        ALLEXCEPT(
            Table_1,
            Table_1[StudentID]
        )
    )

    RETURN
        IF(
            __check_fail_or_pass = 1,
            "Pass",
            "Fail"
        )
     
    Output

     

3 Replies

  • some_bih's avatar
    some_bih
    Community Champion
    2 years ago

    Hi afaherty one possible solution is to create measure Measure_Fail and create new column, like Measure_Catagory with reference to that measure (create measure first) and you should get output

    Measure_Catagory = [Measure_Fail]

     

    Measure_Fail =
    VAR __check_fail_or_pass =
    CALCULATE(
        DISTINCTCOUNT(Table_1[PassFail]),
        Table_1[PassFail] = "Pass",
        ALLEXCEPT(
            Table_1,
            Table_1[StudentID]
        )
    )

    RETURN
        IF(
            __check_fail_or_pass = 1,
            "Pass",
            "Fail"
        )
     
    Output

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, afaherty 

    Based on your description and the data information you provided, I used the following sample data:

    I created a calculated column using the following DAX expression:

    Column = 
VAR _table = FILTER(ALL('Table'),'Table'[StudentID]=EARLIER('Table'[StudentID]))
RETURN IF("pass" IN SELECTCOLUMNS(_table,"aa",'Table'[PassFail]),"Pass","Fail")

    Here are the results:

    I've uploaded the PBIX file I used this time below.

     

     

    How to Get Your Question Answered Quickly

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

    Best Regards

    Jianpeng Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.