topic Re: SUMX formula to iterate if specific conditions are met? in DAX Commands and Tips

SUMX formula to iterate if specific conditions are met?

Anonymous — Wed, 25 Sep 2024 07:19:54 GMT

Hello, just recently started using Power BI and I'm stuck on how I can use it to extract information

Date	ID	Title	Gender	First Name	Last Name	Qual	Grade	Description	GradeValue
16-Mar-2021	12345	Mr	Male	John	Doe	A	F	Fail	0
16-Mar-2022	12345	Mr	Male	John	Doe	A	F	Fail	0
16-Mar-2023	12345	Mr	Male	John	Doe	A	F	Fail	0
16-Mar-2024	12345	Mr	Male	John	Doe	A	P	Pass	1
16-Mar-2025	12345	Mr	Male	John	Doe	A	F	Fail	0
16-Mar-2026	12345	Mr	Male	John	Doe	B	F	Fail	0

I have my table setup similar to this. I need to be able to write a formula that looks at the ID and the Qual, if they are the same then add up the Grade Value. And if the total grade value is 2 then it's a pass, anything less then it's a fail. Also I need to count it as 1 register for each Qual. Any help would be much appreciated, thank you!

Re: SUMX formula to iterate if specific conditions are met?

HotChilli — Wed, 25 Sep 2024 07:55:46 GMT

Calculated column(s) or measure(s)?

If it's a measure, please show the expected output.

I think the test data should be expanded to include a) another ID b) some data that adds up to 2 otherwise there's no 'pass'

Re: SUMX formula to iterate if specific conditions are met?

Anonymous — Wed, 25 Sep 2024 08:31:58 GMT

I have expanded the table

Date	ID	Title	Gender	First Name	Last Name	Qual	Grade	Description	GradeValue
16-Mar-2021	12345	Mr	Male	John	Doe	A	F	Fail	0
16-Mar-2022	12345	Mr	Male	John	Doe	A	P	Pass	1
16-Mar-2023	12345	Mr	Male	John	Doe	A	F	Fail	0
16-Mar-2024	12345	Mr	Male	John	Doe	A	P	Pass	1
16-Mar-2025	12345	Mr	Male	John	Doe	A	F	Fail	0
16-Mar-2026	12345	Mr	Male	John	Doe	B	F	Fail	0
16-Mar-2021	54321	Mr	Male	Phil	Reed	A	F	Fail	0
16-Mar-2022	54321	Mr	Male	Phil	Reed	A	F	Fail	0
16-Mar-2023	54321	Mr	Male	Phil	Reed	A	F	Fail	0
16-Mar-2024	54321	Mr	Male	Phil	Reed	A	P	Pass	1
16-Mar-2025	54321	Mr	Male	Phil	Reed	A	F	Fail	0
16-Mar-2025	54321	Mr	Male	Phil	Reed	B	P	Pass	1

The expected output would be something like this

ID	Title	Gender	First Name	Last Name	Qual	Grade	Grade Value
12345	MR	Male	John	Doe	A	Pass	2
54321	MR	Male	Phil	Reed	A	Fail	1
12345	MR	Male	John	Doe	B	Fail	0
54321	MR	Male	Phile	Reed	B	Pass	1

Re: SUMX formula to iterate if specific conditions are met?

HotChilli — Wed, 25 Sep 2024 11:18:20 GMT

Thanks for the data - that is better than 99% of the posts on the forum (sorry, other posters).

So you want a table visual with the first 6 fields from the data table. Grade Value looks like an ordinary SUM aggregation on the field.

I don't understand the 'grade' column. Is it the Description column from the table? In which case, fine I think the expected output is achieved. However, I don't think the requirement:

" if the total grade value is 2 then it's a pass, anything less then it's a fail" has been addressed.

Thoughts?

Re: SUMX formula to iterate if specific conditions are met?

Anonymous — Wed, 25 Sep 2024 11:26:48 GMT

I was thinking I could have the Grade column read the value of the Grade Value in the new table and then give a Pass or Fail.

Re: SUMX formula to iterate if specific conditions are met?

HotChilli — Wed, 25 Sep 2024 11:45:27 GMT

What I'm saying is the Grade column in the expected results looks like the original 'description' column, it doesn't look like a new measure which looks at the 'Grade Value' column in the visual and assigns pass or fail.

My evidence is the last line which shows Grade Value 1 and 'pass'

Re: SUMX formula to iterate if specific conditions are met?

Anonymous — Wed, 25 Sep 2024 11:49:22 GMT

Ah yes I get what you mean

Date	ID	Title	Gender	First Name	Last Name	Qual	Grade	Description	GradeValue
16-Mar-2021	12345	Mr	Male	John	Doe	A	F	Fail	0
16-Mar-2022	12345	Mr	Male	John	Doe	A	P	Merit Pass	1
16-Mar-2023	12345	Mr	Male	John	Doe	A	F	Fail	0
16-Mar-2024	12345	Mr	Male	John	Doe	A	P	Pass	1
16-Mar-2025	12345	Mr	Male	John	Doe	A	F	Fail	0
16-Mar-2026	12345	Mr	Male	John	Doe	B	F	Fail	0
16-Mar-2021	54321	Mr	Male	Phil	Reed	A	F	Fail	0
16-Mar-2022	54321	Mr	Male	Phil	Reed	A	F	Fail	0
16-Mar-2023	54321	Mr	Male	Phil	Reed	A	F	Fail	0
16-Mar-2024	54321	Mr	Male	Phil	Reed	A	P	Distinction	1
16-Mar-2025	54321	Mr	Male	Phil	Reed	A	F	Fail	0
16-Mar-2025	54321	Mr	Male	Phil	Reed	B	P	Pass	1

Sorry I forgot to include the different types of passes you can get.

Re: SUMX formula to iterate if specific conditions are met?

Anonymous — Thu, 26 Sep 2024 08:20:42 GMT

Thanks for the reply from HotChilli , please allow me to provide another insight:
Hi Anonymous ,

Here are the steps you can follow：

1. Create calculated column.

Test1 = SUMX( FILTER(ALL('Table'), 'Table'[ID]=EARLIER('Table' [ID])&&'Table'[Qual]=EARLIER('Table'[Qual])),[GradeValue])

Test2 = var _maxpdate= MAXX(FILTER(ALL('Table'),'Table'[ID]=EARLIER('Table'[ID])&&'Table'[Qual]=EARLIER('Table'[Qual])&&[Grade]="P"),[Date]) var _maxnodate= MAXX(FILTER(ALL('Table'),'Table'[ID]=EARLIER('Table'[ID])&&'Table'[Qual]=EARLIER('Table'[Qual])),[Date]) return IF( [Test1]=2 && [Date]=_maxpdate,1, IF( [Test1]<2&&[Date]=_maxnodate,1,0))

2. Create calculated table.

Table 2 = var _table= FILTER( 'Table',[Test2]=1) return SUMMARIZE( _table, [ID],[Title],[Gender],[First Name],[Last Name],[Qual], "Grade",MAXX('Table',[Description]), "Grade Value",MAXX('Table',[Test1]))

3. Result:

Best Regards,

Liu Yang

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