Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Issue with dates filters

Hi Expert   I have managed to write most the DAX for the URL filtering and all of the formula is working fine up until the point where the data filter oDAte kicks in. then nothing happens when i cl...
Need Help
v-lili6-msft's avatar
v-lili6-msft
Community Support
7 years ago

hi, Anonymous 

If change the format of "oDate" and then try it again.

and could you share your sample pbix file?

 

Best Regards,

Lin

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    hi lin

     

    did you get the pbix file??

    • v-lili6-msft's avatar
      v-lili6-msft
      Community Support
      7 years ago

      HI, Anonymous 

      The problem is the format of your date in the formula. Just do some adjust as below:

      Report Filter = 
    VAR oL = "DIMSalesPerson/OfficeLocation eq '" & SELECTEDVALUE('DIMSalesPerson'[OfficeLocation]) & "'" 
    VAR oC = "DIMSalesPerson/City eq '" & SELECTEDVALUE('DIMSalesPerson'[City]) & "'" 
    VAR oS  = "DIMShipper/ShipCompanyName eq '" & SELECTEDVALUE('DIMShipper'[ShipCompanyName]) & "'"
    VAR oP = "DIMProduct/CategoryName eq '" & SELECTEDVALUE('DIMProduct'[CategoryName]) & "'"
    VAR oSP = "DIMSalesPerson/SalesPerson eq '" & SELECTEDVALUE('DIMSalesPerson'[SalesPerson]) & "'"
    VAR oRegion = "DIMSalesPerson/SalesRegion eq '" & SELECTEDVALUE('DIMSalesPerson'[SalesRegion]) & "'"
    VAR oDate = if(ISFILTERED('Calendar'[Date]), "Time/Date le "  & FORMAT(MAX('Date'[Date]),"YYYY-MM-DD") & " and Time/Date ge " & FORMAT(MIN('Date'[Date]),"YYYY-MM-DD"))
    VAR oDate = if(ISFILTERED('Calendar'[Date]), "Calendar/Date le "  & MAX('Calendar'[Date]) & " and Calendar/Date ge " & MIN('Calendar'[Date]))
    RETURN

    [Report URL] & 
    SWITCH(
        TRUE, 
            ISFILTERED('DIMSalesPerson'[OfficeLocation]) && ISFILTERED('DIMSalesPerson'[City]), "?filter=" & oL & " and " & oC & " and " & oS & " and " & oP & " and " & oSP & " and " & oRegion & " and " & oDate,
            ISFILTERED('DIMSalesPerson'[OfficeLocation]), "?filter=" & oL, 
            ISFILTERED('DIMSalesPerson'[City]), "?filter=" & oC,
            ISFILTERED('DIMShipper'[ShipCompanyName]), "?filter=" & oS,
            ISFILTERED('DIMProduct'[CategoryName]), "?filter=" & oP,
            ISFILTERED('DIMSalesPerson'[SalesPerson]), "?filter=" & oSP,
            ISFILTERED('DIMSalesPerson'[SalesRegion]), "?filter=" & oRegion,
            oDate
        
        )

      and here is a similar post I had solved for you refer to:

      https://community.powerbi.com/t5/Service/Sharing-a-Pre-Filtered-Report-using-URL-Dates-Between/m-p/693841#M68222

       

      Best Regards,

      Lin