Forum Discussion

akwang's avatar
akwang
Advocate II
8 years ago
Solved

DAX IF Criteria

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 foll...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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]
)
Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
akwang
Advocate II
8 years ago

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.