cancel
Showing results for
Did you mean:
Post Prodigy

## Count Statuses - DAX Help!!!!

I am currently using an "IF" function to apply a value of "Applied" or "N/A" within a column of a table.

I am looking for some help in a DAX statement that will allow me to do the following:

• Count the number of "Applied"
• Count the number of "N/A"
• Divide each of the above by the total to determine the percenatge of each

1 ACCEPTED SOLUTION
Solution Sage

Something like:

Do similar for N/A and the percentage of these should be trivial from there

10 REPLIES 10
Super User

Create a measure like below,

% =
var __a = CALCULATE(COUNTROWS(Table1),Table1[Status] = "A" )
var __b = CALCULATE(COUNTROWS(Table1),Table1[Status] = "B" )
Return
DIVIDE(__b,__a)

Then change the format as Percentage located under modeling Tab

Regards ,

Siva

Helper V

You will need a few Dax measures to do it

First of all:

`totalRows = CALCULATE(COUNTROWS(yourtable; yourcolumnname = "Applied"; yourcolumnname = "N/A")`

Then count for "Applied" or count "N/A"

`totalApplied = CALCULATE(COUNTROWS(yourtable);yourcolumnname = "Applied")totalNA = CALCULATE(COUNTROWS(yourtable);yourcolumnname = "N/A")`

Percentage

```%Applied = (totalApplied / totalRows) * 100
%NA = (totalNA / totalRows) *  100```

Hope this helps you out?

Post Prodigy

@miltenburger

I think the use of ";" is throwing a wrench into the application of the DAX statements.

Any thoughts?

Helper V

Hi @irnm8dn

Jep, my system needs a " ; "

Your system possibly needs a " , "

Post Prodigy

@miltenburger

This is the statement that I am using to translate a "True/False"

CC = if('KPI Dashboard AD'[(Campaign) CC]=TRUE(), "Applied", "N/A")

Your expressions don't seem compatible with my "IF" statement.

Helper V

@irnm8dn

Do you use it as a measure, or do you create a new column (because you should make a new column)

Then you can put CC instead of yourcolumnname which i used in my measures

Post Prodigy

@miltenburger

The "IF" Statement is a function.

When I follow your suggestion and type the DAX statement, eeverything seems in order until I try to name the column.  It does not autopopulate and the DAX statement fails.

Helper V

@irnm8dn

You should make the IF statement a column, then it works.... should work

Post Prodigy

@miltenburger

Can you offer me a little more context on how to do this?

1.  I have a source column with a value of True, Flase or null

2.  I created a separate column and an "IF" statement to translate "True" to "Applied", else "N/A"

3.  From this column, I want to count the "Applied" and the "N/A" rows

In the DAX statements I am attempting to write, PBI doesn't seem to "recognize" and autopopulate the column  in the DAX statement (step 2, above)

Thanks for sticking with this!!!!

Solution Sage

Something like:

Do similar for N/A and the percentage of these should be trivial from there

Announcements