Forum Discussion
Using two filters with CALCULATE
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
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
Alex
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 :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 :)
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
Alex