cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
TrentS Regular Visitor
Regular Visitor

Should be a Simple Countrows...right?

Afternoon all,

 

Have been reading and searching trying to figure this one out and still failing. TIme for experts!

I have two UNrelated tables. The second table, Month_Count, is for some specific charting. It contains a Date format column with Month-Year. My primary table, Query1 for public purposes, contains a calculated column resulting in the same format date. I need to do a count of the times each month-year appears.

 

image.png

 

If I relate the Month_Count to either (Query1[Month_Open]) or [Month_Close], I can get the right number. The other field simply matches it so I know I need another function in there. (Yes I want the "closed" to be a negative number.) Obviously, I am just getting a count of all rows without the relation as it appears in the picture.

Here is the DAX from the (Query1 [Month_Open] ) just in case:

Month_Open = DATE(YEAR(Query1[Open_Date_Non_Blank]),MONTH(Query1[Open_Date_Non_Blank]),1)
As I said all of the relevant columns are the same Date format and since it works with a direct relation, I don't think thats the issue.
What am I missing?
 
Thanks,
TrentS
1 ACCEPTED SOLUTION

Accepted Solutions
jericacoleman Frequent Visitor
Frequent Visitor

Re: Should be a Simple Countrows...right?

You need to set the count filter so it is only grabbing rows equal to the month on the first column. I would use the following formula to achieve what you are looking for. This is similar to doing a COUNTIF in Excel.

 

New_Cases = CALCULATE(COUNT(‘Query1’[Month_Open]),FILTER(‘Query1’,'Query1'[Month_Open]='New Table'[Month]))

 

I didn't know the name of your new table, so I just titled it new table in my formula.

2 REPLIES 2
jericacoleman Frequent Visitor
Frequent Visitor

Re: Should be a Simple Countrows...right?

You need to set the count filter so it is only grabbing rows equal to the month on the first column. I would use the following formula to achieve what you are looking for. This is similar to doing a COUNTIF in Excel.

 

New_Cases = CALCULATE(COUNT(‘Query1’[Month_Open]),FILTER(‘Query1’,'Query1'[Month_Open]='New Table'[Month]))

 

I didn't know the name of your new table, so I just titled it new table in my formula.

TrentS Regular Visitor
Regular Visitor

Re: Should be a Simple Countrows...right?

@jericacoleman 

 

Ah...I got hung up on a Countrows not a simple count.

Many thanks.

Happily accepted as a solution!

 

TrentS

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 355 members 3,424 guests
Please welcome our newest community members: