cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Regular Visitor

## Help with dax to calculate conditional statement by month

I have a problem getting the right formula in my report.  I only have two tables, the activity table then just a standard date table to slice time frames.

ActivityTable:                                        DateTable

Day                                                       Month&Year etc.

Amount

Flag

The records are a list of days, an amount for that day, and a Y or N to indicate if it exceeded a threshhold.  Looks like this

example:

 1-Jan 250 n 2-Jan 0 n 3-Jan 0 n 4-Jan 750 y

The end result I am trying to build is to get to by month, what percentage of days exceed the threshold

so like this:   And would need it to do this for each month as dates are added.  Basically count how many days within a month/year had activity, then calculate the percentage of those days that had "Y's" flag.  So in this example, January had for activity dates and the threshhold was 500, and only 1 of the 4 exceeded that (Y)

 Jan-23 25%

I thought I could count # rows by filtering the table for Y but when I do that, it is not by month.  It it just the whole table count of Ys.

Does anyone know how to do this?  Thanks very much.  New to dax and it is very confusing switching from tableau 🙂

1 ACCEPTED SOLUTION
Super User

Hi,

Here is one way to do this:

Dax:

Measure 41 = var _true = COUNTROWS(FILTER('Table (28)','Table (28)'[bool]="y")) return

DIVIDE(_true,COUNTROWS('Table (28)'))

End result:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

Proud to be a Super User!

2 REPLIES 2
Super User

Hi,

Here is one way to do this:

Dax:

Measure 41 = var _true = COUNTROWS(FILTER('Table (28)','Table (28)'[bool]="y")) return

DIVIDE(_true,COUNTROWS('Table (28)'))

End result:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

Proud to be a Super User!

Regular Visitor

You are amazing!  Thanks so much - this worked perfectly!!

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors