cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Concat Member
Member

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?

1 ACCEPTED SOLUTION

Accepted Solutions
AlexChen Member
Member

Re: 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)))

 

1.png

 

Best Regards

Alex

9 REPLIES 9
Super User
Super User

Re: Using two filters with CALCULATE

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

Thanks & Regards,
Bhavesh

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.
AlexChen Member
Member

Re: Using two filters with CALCULATE

Hi,

 

I assume your table like below.

 

SheetTable:

 

1.png

 

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.

 

2.png

 

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

 

Concat Member
Member

Re: Using two filters with CALCULATE

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:

 

2016-08-26_07h49_26.png

 

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

 

2016-08-26_07h50_50.png

 

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

 

2016-08-26_07h55_13.png

 

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.

 

2016-08-26_07h58_59.png

Then, adding another column following the same logic: 

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

 

2016-08-26_08h01_22.png

 

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))))

 

2016-08-26_08h07_23.png

 

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)).

 

2016-08-26_09h01_47.png

 

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...

scottsen Senior Member
Senior Member

Re: Using two filters with CALCULATE

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 Smiley Happy

Concat Member
Member

Re: Using two filters with CALCULATE

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.

scottsen Senior Member
Senior Member

Re: Using two filters with CALCULATE

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

 

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).

 

AlexChen Member
Member

Re: 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)))

 

1.png

 

Best Regards

Alex

Concat Member
Member

Re: Using two filters with CALCULATE

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.

Highlighted
twahl Frequent Visitor
Frequent Visitor

Re: Using two filters with CALCULATE

Is there a way to apply filters from another table? 

 

Column = CALCULATE(
                    count('Table1'[ColumnID]),
                   'Table1'[ColumnStatus] = "Inactive" && Table2[Columnflag] = 7
)