Forum Discussion
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:
CALCULATE(Counta(Sheet1[StringValue]),ALLEXCEPT('Table','Table'[Column1]))
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:
CALCULATE(Counta(Sheet1[StringValue]),ALLEXCEPT('Table','Table'[Column1]),FILTER(Sheet1,Sheet1[Date]>=[Measure])
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
Alex
10 Replies
I would be happy to help if snapshot of the data is provided and your requirements.
Hi,
I assume your table like below.
SheetTable:
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
AlexHi 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 :manvery-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...
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 :)
Is there a way to apply filters from another table?
Column = CALCULATE( count('Table1'[ColumnID]), 'Table1'[ColumnStatus] = "Inactive" && Table2[Columnflag] = 7
)
