cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
staceyking Frequent Visitor
Frequent Visitor

IF statement

I need to create a measure that calculates a percentage but I need to use an if statement. I have calculated the count of each field but I'm not sure how to add the if statement. Below is what I have so far.

 

=CALCULATE(COUNTA([myvalue]))/CALCULATE(COUNTA([ORGACCOUNTNUMBER]))

 

The if would apply to the [myvalue] that has a value of Completed.

 

I am new to DAX and not sure how to do this.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: IF statement

@staceyking 

 

Try this 

 

Divide(
CALCULATE (COUNTA (WP_plan[MyColumn]), WP_plan[MyColumn] = "Completed"),
CALCULATE(COUNTA(WP_plan[ORGACCOUNTNUMBER])),
0)

 

Try this : If ORGACCOUNTNUMBER is Interger value

 

Divide(
CALCULATE (COUNTA (WP_plan[MyColumn]), WP_plan[MyColumn] = "Completed"),
CALCULATE(COUNT(WP_plan[ORGACCOUNTNUMBER])),
0)

11 REPLIES 11
Super User
Super User

Re: IF statement

@staceyking please spend some time on this blogs it will help to move further in DAX.

https://docs.microsoft.com/en-us/dax/if-function-dax

durschel Frequent Visitor
Frequent Visitor

Re: IF statement

DIVIDE (
    CALCULATE ( COUNTA ( [myvalue] , [myvalue = "Completed"] ) ),
    COUNTA ( [myvalue] )
)

 

In this case, you really don't need an IF statement, but rather to filter your data on your condition. 

staceyking Frequent Visitor
Frequent Visitor

Re: IF statement

Doesnt quite get me there. Here is what I tried.

 

=Divide (
 CALCULATE(COUNTA ([myvalue], [myvalue = "Completed"])) / CALCULATE(COUNTA([ORGACCOUNTNUMBER]))

 

It just gives an error that it's incomplete.

staceyking Frequent Visitor
Frequent Visitor

Re: IF statement

Doesnt quite get me there. Here is what I tried.

 

=Divide (
 CALCULATE(COUNTA ([myvalue], [myvalue = "Completed"])) / CALCULATE(COUNTA([ORGACCOUNTNUMBER]))

 

It just gives an error that it's incomplete.

Super User
Super User

Re: IF statement

@staceyking

 

What is [myvalue] here ? it is measure or column ?

 

Better can you please share the sample table. 

 

It will help us to close this issue asap.

durschel Frequent Visitor
Frequent Visitor

Re: IF statement

 

This:

 

 

=Divide (
 CALCULATE(COUNTA ([myvalue], [myvalue = "Completed"])) / CALCULATE(COUNTA([ORGACCOUNTNUMBER]))

 

is missing the closing parenthesis.


@staceyking wrote:

Doesnt quite get me there. Here is what I tried.

 

=Divide (
 CALCULATE(COUNTA ([myvalue], [myvalue = "Completed"])) / CALCULATE(COUNTA([ORGACCOUNTNUMBER]))

 

It just gives an error that it's incomplete.


 

staceyking Frequent Visitor
Frequent Visitor

Re: IF statement

myvalue is a column. The value could be outstanding or completed. I need to do this calculation based on a completed task

Highlighted
staceyking Frequent Visitor
Frequent Visitor

Re: IF statement

I have tried to use this as well

=CALCULATE (COUNTA (WP_plan[MyColumn], [MyColumn = "Completed"])) / CALCULATE(COUNTA(WP_plan[ORGACCOUNTNUMBER]))

The error says the maximum arguement count for the function is 1.

MyColumn is a column in the table. The values could be Outstanding or Complete. I need to do this calculation on Completed.

 

durschel Frequent Visitor
Frequent Visitor

Re: IF statement

You have the parenthesis in the wrong place.

 

=CALCULATE (COUNTA (WP_plan[MyColumn] ) , [MyColumn = "Completed"]) / CALCULATE(COUNTA(WP_plan[ORGACCOUNTNUMBER]))

I'd also recommend this free Intro to Dax online video:

https://www.sqlbi.com/training/dax/

 

And this DAX documentation, which is a little friendlier than the official:
https://dax.guide/

 

Best of luck!