Create a new column with conditionals on other column

[Jateoh]

The hypothetical situation is about identifying students to enrol for tuition classes based on a few criteria:

Student must have attemped more than one test paper for the subject, and scored between 50 and 60 for at least one of the attempts. I need help on the DAX formula that would allow me to generate the highlighted column (see sample below).

If a student has only attempted one test paper for the subject, or did not score between 50 and 60 for any of the attempts, will not be enrolled for tuition.

Below is an illustration of the example and expected output (i.e. column highlighted).

[Anonymous · Accepted solution]

Hi @Jateoh ,

Your solution is great, @lbendlin. It worked like a charm! Here I have another idea in mind, and I would like to share it for reference.

I think you can also use DAX code. So I create a table and a measure.

Measure = 
VAR _Attempts =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        ALLEXCEPT ( 'Table', 'Table'[Name], 'Table'[Subject] )
    )
VAR _Score =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        'Table'[Score] >= 50
            && 'Table'[Score] <= 60,
        ALLEXCEPT ( 'Table', 'Table'[Name], 'Table'[Subject] )
    )
RETURN
    IF ( _Attempts > 1 && _Score > 0, "Yes", "No" )

Then you can get what you want.

Best Regards

Yilong Zhou

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

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k0syVDSUXJMSqoEUuYGSrE66IKmpsiCTvlQUQtkUeeMxKKczFSQuBEOcRNkcZfEHJCghQE2QVMsgqbmYEHXvPSczGJMFyPEYe4zM8UhYWKEJoHkRgs0KZjl6Fpg4sZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Subject = _t, Name = _t, Score = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Score", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Subject", "Name"}, {{"rows", each _, type table [Score=nullable number]},{"To enrol for tuition", each Table.RowCount(_)*Table.RowCount(Table.SelectRows(_,each List.Contains({50..60},[Score])))>1, type logical}}),
    #"Expanded rows" = Table.ExpandTableColumn(#"Grouped Rows", "rows", {"Score"}, {"Score"})
in
    #"Expanded rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.