Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Visual should not filter after date selection from Slicer

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

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

5 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        4 years ago

        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's avatar
    Anonymous
    Not applicable
    4 years ago

    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