- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Using two filters with CALCULATE

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Concat

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-24-2016
10:10 AM

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?

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

AlexChen

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-29-2016
01:36 AM

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

9 REPLIES 9

BhaveshPatel

Super User

Re: Using two filters with CALCULATE

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-24-2016
03:01 PM

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.

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

Re: Using two filters with CALCULATE

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-26-2016
02:31 AM

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

Concat

Member

Re: Using two filters with CALCULATE

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-26-2016
08:06 AM

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 )

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

Re: Using two filters with CALCULATE

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-26-2016
08:47 AM

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

Concat

Member

Re: Using two filters with CALCULATE

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-26-2016
10:38 AM

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

Re: Using two filters with CALCULATE

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-26-2016
03:50 PM

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

AlexChen

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-29-2016
01:36 AM

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

Concat

Member

Re: Using two filters with CALCULATE

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-29-2016
06:42 AM

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

Re: Using two filters with CALCULATE

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-13-2018
12:13 PM

Is there a way to apply filters from another table?

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

)