cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
staceyking
New Member

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
Baskar
Resident Rockstar
Resident Rockstar

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

View solution in original post

11 REPLIES 11
Anonymous
Not applicable

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. 

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
Not applicable

 

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.


 

Baskar
Resident Rockstar
Resident Rockstar

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

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
Not applicable

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!

 

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
Resident Rockstar
Resident Rockstar

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

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

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
Resident Rockstar
Resident Rockstar

@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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors