I'm working on creating an excel dashboard with power pivots/charts. The data set is for opportunities and contains data such as Business Sector, Operating Group, Opportunity ID, Opportunity Name, Status Reason, and Expected RFP date. These are column headers.
I created a measure that counts rows based on text contained within the Status Reason column for "In Progress". This is the measure I used:
=CALCULATE(COUNTROWS(TABLE),TABLE[COLUMN] = "TEXT")
*actual measure*
=CALCULATE(COUNTROWS(Opportunities), Opportunties[Status Reason] = "In Progress")
This gave me what I needed, but I then realized I needed to filter is down once more based on the Expected RFP date. My data has rows missing dates in that column, so I need to count all rows with a "In Progress" status but filter out those rows missing a date with that status. I, for the life of my, can not get this to work.
I would normally use Power Query to filter out those rows, but I need the data in other columns for those as having that info triggers actions for people to go back to our system of record and input missing info (dates).
Help - Thanks
Hi Sid,
Have you tried adding a filter to calculate for && NOT(ISBLANK([date]))
Mynda
Hi Mynda,
I have not, but I will.
I did try this measure, and it appears it works, but I'm not 100%.
=CALCULATE(COUNTROWS(Opportunities), FILTER(Opportunities, Opportunties[Status Reason] = "In Progress"),Opportunties[RFP Date - Expected]>DATE(1950,1,1))
Once I double-click the calculated number in the pivot table and then filter to double-check the count, in some cases, it matches the calculated amount, and in others, the pivot count is greater than I would have expected based on my manual filtering count.
Hi Sid,
The formula isn't written correctly. If your criteria are AND, you must separate them with && e.g.
=CALCULATE(COUNTROWS(Opportunities), Opportunties[Status Reason] = "In Progress" && Opportunties[RFP Date - Expected]>DATE(1950,1,1))
Mynda
I can't believe these problem is stumping me so much. Below are the two options I have tried to 1) count rows based on a status that also contains a date in the expected RFP date column. Essentially, only counts rows with a date in one column and a status of "in progress" in another.
I believe either formula below should result in the same answer, but I receive this error for the first tone:
"Calculation error in measure 'Opportunities'[Test InProg]: The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression."
And I receive this error don't the second.
"Failed to resolve name 'Opportunities'. It is not a valid table, variable, or function name."
When I rewrite the name to fix that, I then get the same error as the first one. So frustrating!!
1st Formula
=CALCULATE(COUNTROWS(Opportunties), Opportunties[Status Reason] = "In Progress" && NOT(ISBLANK(Opportunties[RFP Date - Expected])))
2nd Formula
=CALCULATE(COUNTROWS(Opportunities), Opportunties[Status Reason] = "In Progress" && Opportunties[RFP Date - Expected]>DATE(1950,1,1))
Fairly certain i resolved my issue with this formula.
Countrow of the table and then filter based on status and a > than date.
=CALCULATE(COUNTROWS(Opportunities),FILTER(Opportunities,Opportunities[Status Reason]="In Progress" && Opportunities[RFP Date - Expected]>DATE(1950,1,1)))
Thanks a million, Mynda, for the help!
Glad you go there in the end, Sid. Tenacity will get you a long way
