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