Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DuranRoberts
New Member

Date Filter not working with CALCULATE, FILTER, then ALLSELECTED

Description:

I need help trying to figureout my issue with a visual not displaying after I use a date filter. Below is my simple measure for counting the unique ID while filtering ALLSELECTED within the given Year/Month (202111). My result is a running total that doesn't care about the year so that I can keep it adding for any given time range, instead of it splitting per year (which is the default calculation). I can't use ALL in my FILTER because none of the page filters will function.Blank visual when filtering CreatedDt only.Blank visual when filtering CreatedDt only.

 

Problem:

My problem is that when I have a change my date range filter (CreatedDt Filter) everything in my visual goes blank, but when I use any other filter (i.e. Carrier, Agent, etc.) those will work for filtering. It seems that its only the date filter and when the measure has ALLSELECTED in it. 

 

Measure Calculation:

QuoteRequestId running total in CreatedDtMonth =

CALCULATE ( COUNT( 'vw_rpt_AllSubmissions'[QuoteRequestId] ),

FILTER ( ALLSELECTED ( 'vw_rpt_AllSubmissions' ), 'vw_rpt_AllSubmissions'[CreatedDtYearMonth] <= MAX ( 'vw_rpt_AllSubmissions'[CreatedDtYearMonth] ) ) )

1 ACCEPTED SOLUTION
DuranRoberts
New Member

I have actually found my own solution to this issue, see below for my answer:

 

Calculation Solution:

CALCULATE ( COUNT( 'vw_rpt_AllSubmissions'[QuoteRequestId] ), FILTER ( ALLEXCEPT( vw_rpt_AllSubmissions,vw_rpt_AllSubmissions[PayToCarrierName],vw_rpt_AllSubmissions[AgencyName], vw_rpt_AllSubmissions[AgentFullName],vw_rpt_AllSubmissions[CreatedDt] ), 'vw_rpt_AllSubmissions'[CreatedDtYearMonth] <= MAX ( 'vw_rpt_AllSubmissions'[CreatedDtYearMonth] ) ) )

 

Solution Description:

I have come up with the above work around that allows me to add the listed Page Filters into my DAX calculated column so that it manually accepts what is being used. This isn't the best case scenario because I'll have to add it each time that I change my page filters but I won't be doing that often. I hope someone else can benefit from this solution and please leave any comments if a better solution comes up.

Screen Shot 2021-11-29 at 9.09.22 AM.png

View solution in original post

3 REPLIES 3
DuranRoberts
New Member

I have actually found my own solution to this issue, see below for my answer:

 

Calculation Solution:

CALCULATE ( COUNT( 'vw_rpt_AllSubmissions'[QuoteRequestId] ), FILTER ( ALLEXCEPT( vw_rpt_AllSubmissions,vw_rpt_AllSubmissions[PayToCarrierName],vw_rpt_AllSubmissions[AgencyName], vw_rpt_AllSubmissions[AgentFullName],vw_rpt_AllSubmissions[CreatedDt] ), 'vw_rpt_AllSubmissions'[CreatedDtYearMonth] <= MAX ( 'vw_rpt_AllSubmissions'[CreatedDtYearMonth] ) ) )

 

Solution Description:

I have come up with the above work around that allows me to add the listed Page Filters into my DAX calculated column so that it manually accepts what is being used. This isn't the best case scenario because I'll have to add it each time that I change my page filters but I won't be doing that often. I hope someone else can benefit from this solution and please leave any comments if a better solution comes up.

Screen Shot 2021-11-29 at 9.09.22 AM.png

amitchandak
Super User
Super User

@DuranRoberts ,

 

Try running total with date table. example

 

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected('Date'),'Date'[date] <=max('Date'[date])))

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

Hello @amitchandak,

I appreciate your feedback and response, but this was the calculation I used to get where I'm at. The ALLSELECTED is what wasn't working with my CreatedDt (Date) filter. I have since figured out my work around for this problem that seens to be working for now. I just needed to use the ALLEXCEPT function and list my page filters that I'm using. I'll submit it as my answer shortly and thanks again for your help, hopefully others will find this useful as well.

 

Thanks,

Duran

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.