topic Insert Column Help in DAX Commands and Tips

Insert Column Help

afaherty — Wed, 29 May 2024 20:08:32 GMT

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

Re: Insert Column Help

Anonymous — Thu, 30 May 2024 05:36:22 GMT

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.

Re: Insert Column Help

some_bih — Thu, 30 May 2024 05:44:42 GMT

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

Re: Insert Column Help

ThxAlot — Thu, 30 May 2024 10:33:59 GMT