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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
jenniferhoward
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-Jan250n
2-Jan0n
3-Jan0n

4-Jan

 

750y

 

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-2325%

 

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
ValtteriN
Super User
Super User

Hi,

Here is one way to do this:

ValtteriN_0-1676299259194.png

Dax:

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

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

End result:

ValtteriN_1-1676299308406.png



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!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
ValtteriN
Super User
Super User

Hi,

Here is one way to do this:

ValtteriN_0-1676299259194.png

Dax:

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

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

End result:

ValtteriN_1-1676299308406.png



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!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors