cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
danb27 Frequent Visitor
Frequent Visitor

create a table based on a query, keeping filters on page

Say I have Query1, which looks like this:

date, origin, type, cnt 
5/1/2019, A, A, 6
4/29/2019, B, A, 6
4/29/2019, C, B, 3

And I have three filters on the report where users can define a date range, what origins, and types to consider.

Now I need to create a second table (DailyFiltered) that looks like this when no filters are applied:

date, filteredCnt
5/1/2019, 6
4/29/2019, 9

While considering what the user defines in the report filters. For example, if the user were to select only type A, then Daily Filtered would instead look like this:

date, filteredCnt
5/1/2019, 6
4/29/2019, 6

Currently, I have:

Daily Filtered = summarize(keepfilters(Query1), Query1[date], 
    "filteredCnt", sum(Query1[cnt]))

And I have Query1 and Daily Filtered joined on date, many to one, cross filter direction = Both.

The second table is only being filtered by the date filter for Query1. Not sure where I went wrong.

6 REPLIES 6
Super User III
Super User III

Re: create a table based on a query, keeping filters on page

The approach of creating a new calculated table will not work. Calculated tables are evaluated when the data is refreshed, they are not re-calculated any time a filter is changed.

 

But you don't need a separate table for this, you could just create a measure to do the daily totals.

 

eg.

 

Daily Cnt = CALCULATE( SUM( Query1[Cnt] ), ALL( Query1 ), VALUES( Query1[date] ) )

Super User IV
Super User IV

Re: create a table based on a query, keeping filters on page

Hi,

Just drag Date to your visual and then use this measure

=SUM(Data[Cnt])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
danb27 Frequent Visitor
Frequent Visitor

Re: create a table based on a query, keeping filters on page

So then follow up question: if I wanted to get the 90th percentile of that measure, how would I do that? 

Super User III
Super User III

Re: create a table based on a query, keeping filters on page


@danb27 wrote:

So then follow up question: if I wanted to get the 90th percentile of that measure, how would I do that? 


With something like the following using the Percentilex.inc function:

 

90th Percentile = PERCENTILEX.INC( Values( Query1[date] ), CALCULATE(SUM(Query1[cnt])), 0.9 )

danb27 Frequent Visitor
Frequent Visitor

Re: create a table based on a query, keeping filters on page

When I try to plot this in a histogram it doesn't work. I am hoping to have a histogram where the X axis is ranges of the sum of all counts and the y axis is the number of dates that fall into each range. Thank you for all the help so far

Super User III
Super User III

Re: create a table based on a query, keeping filters on page


@danb27 wrote:

When I try to plot this in a histogram it doesn't work. I am hoping to have a histogram where the X axis is ranges of the sum of all counts and the y axis is the number of dates that fall into each range. Thank you for all the help so far


That's a completely new requirement. You need to generate a column for the "ranges of sums" to put on the x-axis and create a new measure that counts the dates. I answered a similar question a few weeks ago here https://community.powerbi.com/t5/Desktop/Double-grouping/td-p/672757 you should be able to use the same technique here.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors