Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
afaherty
Helper IV
Helper IV

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
3 ACCEPTED SOLUTIONS
v-jianpeng-msft
Community Support
Community Support

Hi, @afaherty 

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

vjianpengmsft_0-1717047069984.png

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:

vjianpengmsft_1-1717047279693.png

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.

 

 

View solution in original post

some_bih
Super User
Super User

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
some_bih_0-1717047870292.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






View solution in original post

ThxAlot
Super User
Super User

ThxAlot_1-1717065278398.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



View solution in original post

3 REPLIES 3
ThxAlot
Super User
Super User

ThxAlot_1-1717065278398.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



some_bih
Super User
Super User

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
some_bih_0-1717047870292.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






v-jianpeng-msft
Community Support
Community Support

Hi, @afaherty 

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

vjianpengmsft_0-1717047069984.png

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:

vjianpengmsft_1-1717047279693.png

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.

 

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.