Insert Column Help

[afaherty]

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:

StudentID	Date	Test	PassFail	Category
1	1/1/24	Math	Fail	Pass
1	2/1/24	Math	Fail	Pass
1	3/1/24	English	Pass	Pass
2	3/12/24	Writing	Pass	Pass
2	4/12/24	Math	Fail	Pass
3	5/1/24	English	Fail	Fail
3	4/2/24	Writing	Fail	Fail
4	1/2/24	Math	Fail	Pass
4	2/2/24	English	Pass	Pass

[Anonymous · Accepted solution]

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 · Accepted solution]

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

[ThxAlot · Accepted solution]

[some_bih · Accepted solution]

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 · Accepted solution]

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.