DAX IF Criteria

[akwang]

Hi All,

So i have a some data with student exam information. Student1 has written 5 exams as follows Math-pass, English-Pass, Geo-pass, His-pass, alg-fail. Student2 has written same exams as follows Math-pass, English-fail, Geo-fail, His-pass, alg-fail.

Now i need to write some DAX that would give overall if student pass or fail based on the following criteria:

1. The must pass Math

2. The must pass Eng

3.The must pass one other subject

So the result would be: Student 1=pass, Student 2=fail.

Any ideas how to approch such a task. thanks

[Anonymous]

I would handle this as part of your data import.  I would attempt to pivot the data such that you have a single row for each student, and the classes as their own column with a Pass/Fail as a true/false field.

From here you can then include a Pass/Fail field that contains logic for the entire row.  This field would be something like (In the add column GUI box that opens up)

= if [mathResult] and [EngResult] and ([GeoResult] or [HisResult] or [AlgResult])

[akwang]

Hi Guys thanks for the replies,

I think i might have doen a bad job explaining my problem. So herei go again with an attached image of my table.

Criterias:

1. must pass Math

2.must pass EngA

3.must pass any other 3 subjects

If they wrote anything less than 5 subjects (which would include Math & EngA) then that would count as fail too.

[Anonymous · Accepted solution]

Ok try this combination of measures:

[PassMath] = CALCULATE(
	COUNTROWS('YourTable'),
	ALLEXCEPT('YourTable', 'YourTable'[Candidate #], 'YourTable'[Subject]),
	'YourTable'[Subject] = "MATH",
	'YourTable'[Pass Exam] = "pass"
) > 0

[PassEnglish] = CALCULATE(
	COUNTROWS('YourTable'),
	ALLEXCEPT('YourTable', 'YourTable'[Candidate #], 'YourTable'[Subject]),
	'YourTable'[Subject] = "ENGA",
	'YourTable'[Pass Exam] = "pass"
) > 0

[Pass3Others] = CALCULATE(
	COUNTROWS('YourTable'),
	ALLEXCEPT('YourTable', 'YourTable'[Candidate #], 'YourTable'[Subject]),
	not 'YourTable'[Subject] = "ENGA",
	not 'YourTable'[Subject] = "MATH",
	'YourTable'[Pass Exam] = "pass"
) > 2

[PassOverAll] = AND(
	AND(
		[PassMath],
		[PassEnglish]
	),
	[Pass3Others]
)

[akwang]

So i finally got the measures to work , however when i checked the results i got 10 persons meeting the 3 criterias out of 72 persons. When i divide and change to % i get 1.39% instead of 13.9%. So i had to multiply by 10 to get correct answer. Any ideas why?

[Anonymous]

I'd guess that the measure you used to get to the 1.39% has an error in it.  What was the formula you used?

[akwang]

hi @Anonymous,

See my measures below. I think it is the PassOverall Measure that has the problem since when on a card it says TRUE instead of giving me the count which is 10.

PassOverAll = (AND(AND([PassMath],[PassEng]),[Pass3 Others]))

PassMath = CALCULATE(
    COUNTROWS('90080-Laventure Results'),
    ALLEXCEPT('90080-Laventure Results', '90080-Laventure Results'[Candidate #], '90080-Laventure Results'[Subject]),
    '90080-Laventure Results'[Subject] = "MATH",
    '90080-Laventure Results'[Pass Exam] = "pass"
)>0

PassEng = CALCULATE(
    COUNTROWS('90080-Laventure Results'),
    ALLEXCEPT('90080-Laventure Results', '90080-Laventure Results'[Candidate #], '90080-Laventure Results'[Subject]),
    '90080-Laventure Results'[Subject] = "ENGA",
    '90080-Laventure Results'[Pass Exam] = "pass"
) >0

Matriculation = DIVIDE([PassOverAll],[Total Candidates])

[akwang]

@Anonymous& @ValentinBIA Still no luck getting the count of 10 for this. Any ideas?

[Anonymous]

@akwang  The Passoverall was designed just to be True/False.  You will need to use it in conjunction with another measure if you want to get that count.  I'd suggest using the PassOverall as a page or visual filter then having a count measure like:

CountPasses = 
VAR passCount = COUNTROWS('90080-Laventure Results')
VAR allCount = CALCULATE(
	COUNTROWS('90080-Laventure Results'),
    ALLEXCEPT('90080-Laventure Results', '90080-Laventure Results'[Candidate #], '90080-Laventure Results'[Subject])
)
RETURN
DIVIDE(passCount, allCount)

[akwang]

hi @Anonymous, when idid that i got 1 instead of 10 passes. Just to clarify, i want to count how much TRUE in the Passcount. Also rather than create the previous 4 measures for passmath,passeng,pass3 other & passcount, would it make sense to create calculated column and then sum the TRUE?

   

[Anonymous]

Making them columns is a bad idea for performance and modeling reasons.  The measures are the way to go.

The specific measure you want for counting ultimately depends on how you are expecting to use the measure.  I'm trying to anticipate how you are using it and my hope is that you would understand the approach and tweak the measure yourself to adjust it for your specific purposes.

Here is a new measure and a specific use case for it:

CountPasses = 
VAR passCount = COUNTROWS('90080-Laventure Results')
VAR allCount = CALCULATE(
	COUNTROWS('90080-Laventure Results'),
    ALL('90080-Laventure Results')
)
RETURN
DIVIDE(passCount, allCount)

Put that measure into the CountPasses card and apply a visual level filter of PassOverAll = True.

[akwang]

Hi @Anonymous,

So when i try to apply the visual filter of passoverall i am not getting the option to adjust the filter on the card. I created a mesure as below: Count = IF([PassOverAll]=TRUE(),COUNT('90080-Laventure Candidate'[Candidate #]),0) which gives the correct value on the table visual but i cant seem to be able to apply same on card visual. I am still a newbie when it comes to DAX so bear with me.

[akwang]

Hi, ireceivedthis error on the pass3others measure: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

[Anonymous]

Thats strange, its working for me.  Just check no comma's are missing.  I know i forgot them the first time i posted the answer, then i had to edit it afterwards.

[akwang]

Still not working.

What i am trying to do is to calculate out of the total students what percentage meets the criteria.

[Anonymous]

Sadly the organisation's security policy for where i work won't allow me to check out your pbix file.

[akwang]

Darn it 😞  How about ifyou send me your pbix that you got it working on, maybe i can compare and see my error.

[Anonymous]

Its a fairly simplistic file but hopefully it helps.  Please let me know if this link works for you:

EDIT: Removed link

[akwang]

yes it worked but i think you selected the wrong file. This one is to monitor pallets

[Anonymous]

Ahh rats you are right.  I was working on another challenge for someone else and I've got my wires crossed.  I didn't save your demo project at the end of the day yesterday.  Sorry.

[ValentinBIA]

Hi @akwang,

I am not sure about the DAX writing of it, but the logic would be the following:

If Math = fail then "fail"

   else if English = fail then "fail"

   else if Geo = pass then "pass"

   else if His = pass then "pass"

   else if Alg = pass then "pass"

   else "fail"

For the DAX formula, not too sure about it but:

IF([Math]="fail","fail",

  IF([English]="fail","fail",

  IF([Geo]="pass","pass",

  IF([His]="pass","pass",

  IF([Alg]="pass","pass","fail")))))