Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

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. 

Anonymous
Not applicable

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.


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


 

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

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!

 

Anonymous
Not applicable

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.

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

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

Anonymous
Not applicable

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

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.