IF statement

[Anonymous]

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.

[Anonymous]

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. 

[Anonymous]

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.

[Anonymous]

This:

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

is missing the closing parenthesis.

---

@Anonymous 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.

---

[Baskar]

@Anonymous

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.

[Anonymous]

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.

[Anonymous]

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!

[Anonymous]

Everytime I try this I get the following error.

Calculation error in measure 'WP_plan'[CircleCaringComp]: The value for 'CircleCaring = "Completed"' cannot be determined. Either 'CircleCaring = "Completed"' doesn't exist, or there is no current row for a column named 'CircleCaring = "Completed"'.

I do know the column exist and there are rows in that column where the value is "Completed".

Going through online documentation it seems this should work. Not sure what the issue is.

[Baskar · Accepted solution]

@Anonymous 

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)

[Anonymous]

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

[Anonymous]

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.

[Baskar]

@Anonymous 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