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

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

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'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.