Showing results for 
Search instead for 
Did you mean: 
Not applicable

Using two filters with CALCULATE

I have large spreadsheet with a date in one column, and a string value in another column and I need to produce a table with count of each string value for a given date range.


So far I have Table with each string value in Column1, and the count of that string value over a given date range to go into Column2.


For Column2, I can get the count for EACH string value while ignoring the date range with this:



And I can get the count for ALL string values in the date range with this:

CALCULATE(Counta(Sheet1[String1]),FILTER(Sheet1,Sheet1[Date]>=[Measure])) (where measure calculates the start of the previous month).  Thsi simply returns the same number in every row of Column2, as I would expect.


The problem is that I can't seem to combine them.  I tried this:



I would expect both filters to apply, but they don't.  It just returns the same thing as CALCULATE(Counta(Sheet1[String1]),FILTER(Sheet1,Sheet1[Date]>=[Measure])).  That is, the same number in every cell as opposed to a count for each string value after the date calculated in Measure.

Any idea what I am doing wrong?


Hi Concat,


Just as what scottsen said, you have to use Measures here not Calculated columns.


By the way, if you want to get the topn records of a table by some conditions such as a specific date as you mentioned, you don't have to use rankx, you can create a table using code below to get top 5 records with filtered date:


Table 2 = TOPN(5, Filter(SheetTable, SheetTable[date] > Date(2016, 7, 1)))




Best Regards


View solution in original post

Frequent Visitor

Is there a way to apply filters from another table? 


                   'Table1'[ColumnStatus] = "Inactive" && Table2[Columnflag] = 7



I assume your table like below.






I create 4 measure according what you described above.


previousMonthStart = CALCULATE(Date(YEAR(TODAY()), MONTH(TODAY()) - 1, 1))


Measure = CALCULATE(Counta(SheetTable[value]),ALLEXCEPT('SheetTable','SheetTable'[Column1]))


Measure 2 = CALCULATE(Counta(SheetTable[value]),FILTER(SheetTable,SheetTable[Date]>=[previousMonthStart]))


Measure 3 = CALCULATE(Counta(SheetTable[value]),ALLEXCEPT('SheetTable','SheetTable'[Column1]),FILTER(SheetTable,SheetTable[Date]>=[previousMonthStart]))


And this is the result below.




In this scenario, if we apply the Column1 on rows, the Measure 2 will be sliced by Column1. So even we didn’t group by Column1 in Measure 2, it can still return same result as Measure 3. What you have done is correct.


Best Regards


Not applicable

Hi Alex,


Thank you for taking the time to review this.


The approach I took was different, and I assume I am not understanding something fundamental because I get different results.  Using your example, below are the steps I took which give different results.


I loaded spreadsheet data as defined your SheetTable:




I then created a new table using this formula:  Table = Summarize(SheetTable,SheetTable[Column1])




Then I created a relationship between this Table and Sheet Table:




From here, I am able to get the count of Column 1 without issue.  I added a new column with this formula:  

CALCULATE(Counta(SheetTable[value]),ALLEXCEPT('Table','Table'[Column1])).  Note that I have named the 2nd column "Measure" for ease of comparison with your table, but it is not actually a measure.  The formula was enetered directly into the column formula.



Then, adding another column following the same logic: 

CALCULATE(Counta(SheetTable[value]),FILTER(SheetTable,SheetTable[Date]>=CALCULATE(Date(YEAR(TODAY()), MONTH(TODAY()) - 1, 1))))




Then, adding another column following the same logic: 

CALCULATE(Counta(SheetTable[value]),ALLEXCEPT('Table','Table'[Column1]),FILTER(SheetTable,SheetTable[Date]>=CALCULATE(Date(YEAR(TODAY()), MONTH(TODAY()) - 1, 1))))




As you can see, I can't seem to create a Table with the slices I need.  Measure 3 returns the exact same values as Measure two, even though I have a second filter in place.  My expectation is that for AA (as an example) it would find all rows where Column1=AA and date > previousMonthStart.


Both Measure and Measure 2 work as I expect them to based on my formulas above.  That is, Measure counts all instances of AA, and Measure 2 counts all instances where date>previousMonthStart.  That is 2 and 6, respetively.  So why does my column Measure 3 fail?  I expect it to return a 1 since that is the result when both conditions are met.


The workaround I came up with was to create another table as an intermidiary.  This second table already had one of the filters applied, so I could get the count by the 2nd filter without issue.  In your example, this 2nd table would have the formula:

Filter(SheetTable,SheetTable[date]>=Date(YEAR(TODAY()), MONTH(TODAY()) - 1, 1)).




As you can see, getting the count from this table is now trivial.  For my purpose, having an additional table isn't a big nuisance, but what if I want to apply 6 filters?  It seems awfully inefficient to keep creating tables with only one filter applied (and why I assume I am not understanding something fundamental Man Very Happy)


I am very new to PowerBI, and the lack of a COUNTIFS() statement seems to be a issue that keeps coming up for me!  Old excel habits die hard...  If only PowerBI let me create a pivot table...

Not applicable

I have a meeting in a few minutes and can't full digest this, but want to make 1 point.


Say you have a calculated column   =SUM(MyTable[MyColumn]).


That will return the SUM of the entire table, on every row of the table.  It's not going to look at "just the current row".  That is because SUM( ) is using a filter context... that doesn't exist when doing a calculated column.


However, if you do   =CALCULATE(MyTable[MyColumn]) ... you will then get just the value of MyColumn (not the sum of the whole table).   The call to CALCULATE takes the current "row context" (as you walk the table 1 row at a time calculating your calculated column) and converts it into a "filter context" that SUM( ) can understand and use.


Note ALSO that if you did:

My Sum := SUM(MyTable[MyColumn]

and then used that new measure on your calculated column   = [My Sum]

you will get the 2nd behavior -- eg, just the sum of the current row.  That is because measures have an "implicit" calculate.


I have no idea if this is related to your problems (could also be that you need to use RELATED somewhere), but my gut is... this info will help get you in the right direction 🙂

Not applicable

Thanks for the insight scottsen.  I am using CALCULATE(), so I would like to think I am on track...


I can get a visualization to filter according to my expectations, as Alex has shown with the measures he has setup...  but I cannot replicate it in a Table without creating intermiadary tables with one filter applied at a time.


Working with a table is advantage because I have more at my disposal to manipulate it further.  For example, in my original spreadsheet I have 30 distinct values.  I only want to show the top 10 counts of these values for a specific date.  To do this, I am using a RANKX() measure and applying it to the filter of the visulaization so that only the top 10 are displayed.  RANKX() needs a table with the counts already established, so I can't work with measures alone.


Of course, I could determine and filter by the top 10 values myself, but this would require manual intervention everytime new data is imported.

Hi Concat,


Just as what scottsen said, you have to use Measures here not Calculated columns.


By the way, if you want to get the topn records of a table by some conditions such as a specific date as you mentioned, you don't have to use rankx, you can create a table using code below to get top 5 records with filtered date:


Table 2 = TOPN(5, Filter(SheetTable, SheetTable[date] > Date(2016, 7, 1)))




Best Regards


Not applicable

Seems I have a bit of reading to do regarding when to use measures and when to use calculated columns...  Well, thank you both for steering me in the right direction.


As it stands right now, my report is fully functional, but I obviously have not employed the most elegant solution.

Not applicable

I feel as if you are (as I like to say) "off in the weeds".  🙂


I'm hampered by 2 things:

* Your use a horrible terms like "measure" (for a column) and Sheet1/Table1, etc.  

* That there is just really really limited number of times that I try to create calculations and push them into a new table.


From  your original mail: "I need to produce a table with count of each string value for a given date range"  -- how do you specify this "date range"?  From the dax you have shown seems like it is simply "everything before today" ?


Think I also need to know the relationships between 'SheetTable' and 'Table'


Also note that you can add aggregate expressions on your Summarize(SheetTable,SheetTable[Column1]), like:

  Summarize(SheetTable,SheetTable[Column1], "The Row Count", COUNTROWS(SheetTable))


(Though, you may need to wrap that countrows in a calculate for the same reason as my last post).


Community Champion
Community Champion

I would be happy to help if snapshot of the data is provided and your requirements.

Thanks & Regards,

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

Helpful resources

June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors