cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
irnm8dn
Post Prodigy
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

 

Thanks in advance.

1 ACCEPTED SOLUTION
jthomson
Solution Sage
Solution Sage

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

View solution in original post

10 REPLIES 10
SivaMani
Memorable Member
Memorable Member

@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


Best Regards,
Siva Mani
miltenburger
Helper V
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?

@miltenburger

 

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

 

Any thoughts?

Hi @irnm8dn

 

Jep, my system needs a " ; "

Your system possibly needs a " , " 

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

@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

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

@irnm8dn

 

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

@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
Solution Sage
Solution Sage

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

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors