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

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

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

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