Visual should not filter after date selection from Slicer

[Anonymous]

Hi,

Is it possible to just  pass values from slicer to calculated column but the visual should not filter for date selected in slicer.

For eg: Below is the visual I got after passing values from date slicer to my calculated column and it filters for that quarter as well

Age of Pending Claims:

Age of Pending Claims = IF(TrusteeClaimsList_Received[LeadLagReceivedDate] <> BLANK()

&& ISBLANK(TrusteeClaimsList_Received[LeadLagDecisionEndDate]),

DATEDIFF(TrusteeClaimsList_Received[LeadLagReceivedDate],[End of Quarter],DAY),BLANK())

End of Quarter is end of quarter from date slicer selected by user

End of Quarter = 

VAR __Date = SELECTEDVALUE('Date Table'[Date])

VAR __Month = MONTH(__Date)

VAR __Year = YEAR(__Date)

RETURN

SWITCH(TRUE(),

__Month <= 3,DATE(__Year,3,31),

__Month <= 6,DATE(__Year,6,30),

__Month <= 9,DATE(__Year,9,30),

DATE(__Year,12,31)

)

Now, I get incorrect results because according to my requirement it should only take values from slicer and should not filter the visual for that quarter

I need to get below results, this is based on start of quarter date = 1/7/2021 and end of quarter date = 30/9/2021 but visual is not filtering for that quarter

[Anonymous · Accepted solution]

I got the solution for this, Its just use RemoveFilters function:

Undetermined End of Period1 =

var EOQ =

SELECTEDVALUE('Date Table'[End of Quarter])

var UEP = CALCULATE(COUNT(TrusteeClaimsList_Received[PolicyCaseBenefitKey]),

REMOVEFILTERS('Date Table'[Quarter & Year]),

TrusteeClaimsList_Received[LeadLagReceivedDate] <> BLANK(),

TrusteeClaimsList_Received[LeadLagReceivedDate] < EOQ,

ISBLANK(TrusteeClaimsList_Received[LeadLagDecisionEndDate])

)

return UEP

[Ashish_Mathur]

Hi,

If you do not want the slicer to filter the visual, you should turn off the interaction.  A slicer selection can be used in a calculated column but a change in the slicer will not reflect in the calculated column formula uness you manually click on Refresh.

[Anonymous]

Hi,

I tried to Turn off interaction. But it is giving me incorrect result.

Average Age of Pending Clims should show below:

But is shows 

Age of Pending cliams is a claculated column which takes slicer selection and calculates

It should only take slicer selection and calculate but Visual should not filter for quarter

[Ashish_Mathur]

Hi,

I cannot understnd anything from the images that you posted.  Share the download link of the PBI file and show the expected result very clearly.

[Anonymous]

Hi,

So, when a user selects Q3 then my measure should take max value/End of Quarter and give me the output as it is without filter between the quarter.

Below, When user selects Q3 my measure should take max value from Q3(i.e, 30/9/2021) and calculate measure and return me result TILL q3 and not between Q3 

UEP =

var EOQ = SELECTEDVALUE('Date Table'[End of Quarter])

var UEP = CALCULATE(COUNT(TrusteeClaimsList_Received[PolicyCaseBenefitKey]),

FILTER(TrusteeClaimsList_Received,TrusteeClaimsList_Received[LeadLagReceivedDate] <> BLANK()),

FILTER(TrusteeClaimsList_Received,TrusteeClaimsList_Received[LeadLagReceivedDate] < EOQ),

FILTER(TrusteeClaimsList_Received,ISBLANK(TrusteeClaimsList_Received[LeadLagDecisionEndDate]))

)

return UEP