CODEWITHSUNDEEP
powerbidax
Duran Roberts
Duran Roberts

Reputation: 11

Date Filter not working with CALCULATE, FILTER, then ALLSELECTED in Power BI

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

Upvotes: 1

Views: 2232

Answers (1)

Duran Roberts
Duran Roberts

Reputation: 11

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.enter image description here

Upvotes: 0

Related Questions