Forum Discussion

dcadwallader's avatar
dcadwallader
Helper I
4 years ago
Solved

Percentage Calc Based on Three Values (Pass, Fail, Not Inspected)

Afternoon,

 

I'm looking to put a Pass Rate Calc in my file that will allow for three values in a single column - Pass, Fail and Not Inspected.

 

For example, lets say I have a sample of 150 cars; 50 of those cars passed inspection, 50 of those cars failed inspection and the remaining 50 were not inspected. As such, my pass rate on this is 50%.

 

I have the below DAX, which I assume should work;

 

Calc Inspection Result Pass Rate =
DIVIDE(
CALCULATE( COUNTROWS('EXTRACT') , 'EXTRACT'[Overall Result - Post Appeal] = "Pass" , COUNTROWS('EXTRACT') , 'EXTRACT'[Overall Result - Post Appeal] = "Fail") ,
CALCULATE( COUNTROWS('EXTRACT') , 'EXTRACT'[Overall Result - Post Appeal] = "Pass") ,
BLANK()
)

 

But I keep getting the below error message - The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column.

 

Any ideas?

  • Hi,

    The problem is with the first calculate, since it has two different COUNTROWS. To solve this use OR in the filter like in this example:

    Data:


    DAX:

    Apples & Oranges =
    DIVIDE(
    CALCULATE(COUNTROWS(Applecount),Applecount[AppleCount]="Apple"),
    CALCULATE(COUNTROWS(Applecount),or(Applecount[AppleCount]="Apple",Applecount[AppleCount]="Orange")),
    BLANK()
    )
     
    End result:


    I hope this helps and if it does consider accepting this as a solution and giving the post a thumbs up!

2 Replies

  • ValtteriN's avatar
    ValtteriN
    Community Champion

    Hi,

    The problem is with the first calculate, since it has two different COUNTROWS. To solve this use OR in the filter like in this example:

    Data:


    DAX:

    Apples & Oranges =
    DIVIDE(
    CALCULATE(COUNTROWS(Applecount),Applecount[AppleCount]="Apple"),
    CALCULATE(COUNTROWS(Applecount),or(Applecount[AppleCount]="Apple",Applecount[AppleCount]="Orange")),
    BLANK()
    )
     
    End result:


    I hope this helps and if it does consider accepting this as a solution and giving the post a thumbs up!