Count Statuses - DAX Help!!!!

[irnm8dn]

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

Thanks in advance.

[SivaMani]

@irnm8dn,

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

[miltenburger]

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?

[irnm8dn]

@miltenburger

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

Any thoughts?

[miltenburger]

Hi @irnm8dn

Jep, my system needs a " ; "

Your system possibly needs a " , " 

[irnm8dn]

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

[miltenburger]

@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

[irnm8dn]

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

[miltenburger]

@irnm8dn

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

[irnm8dn]

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

[jthomson · Accepted solution]

Something like:

Total Applied = calculate(countrows(your table),your table[your column]="Applied")

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