Forum Discussion

rashamohsen's avatar
rashamohsen
Frequent Visitor
6 years ago

Date filters

Hallow everyone

 

I have a problem with my date table, I created two Start date and End date, and they have four fields: Date, Day, Month and Year.

I need to use two filter dates like Start date and End date to filter another table that has two date fileds: Created date and closed date.

What I need is to let the user choose a range of time what ever it is between start and end date.

The user should set the Start date and the End date which are comming from the created Date tables.

I can not use the date fileds from my table, because I want to use these dates like variables in dax for some estimating.

for  that I created a  new column in my table using DAX like:

 

my columns = Var EskBeginn = ('my table'[CreatedDate]) 
Var EskEnde = ('my table'[ClosedDate]) 
Var StartDtum = SELECTEDVALUE('StartDate'[Date])
Var EndDatum = SELECTEDVALUE('EndDate'[Date])
return IF(EskBeginn < StartDtum && EskEnde > StartDtum && EskEnde < EndDatum, 1, IF(EskBeginn > StartDtum && EskBeginn < EndDatum && EskEnde > EndDatum, -1, 0))

 

 

 
 
return IF(EskBeginn < StartDtum && EskEnde > StartDtum && EskEnde < EndDatum, 1,
IF(EskBeginn > StartDtum && EskBeginn < EndDatum && EskEnde > EndDatum, -1, 0))
And the problem is that I can not use StartDate and End date(the created date tables) in Dax,
beacuse it includes many values where Power Bi expects only one value(says power bi).
I tried selectedvalue('StartDate'[Date]), it does not work , it is always empty.
I tried to make a relationship between Start date and my table, it did not work, like that I can not use the Start and End date
slicer, when I use them the entire report will be empty.
I knew that we can not use a refrence filter or parameter for the end use.
Example: to filter created date and closed date with a refrence in the sql, and then make the user give refernce value
in the parameter.
The parameters are only for the users who have power bi desktop, and I do not want to let them have original report.
It would be nice to answer my quistion if anyone know the answer.
 
Best regards
 

 

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    rashamohsen 

    Can you explain it with a sample pbix if possible, which helps us to understand your requirements. 

    Based on the current information, can't you just create a slicer use the date column.

     

     

    Paul Zheng _ Community Support Team
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

    • rashamohsen's avatar
      rashamohsen
      Frequent Visitor
      6 years ago

      Hallo

      Thanks for your reply, but I have a slicer (from my Date tables) like you mentioned, but my problem is that I do not know how to connect or to bind my Date tables with my Customer Table.

      The report is about customer satisfaction.

      I need tow Dates Start and edn date where the user can choose randolmy any date.

      For my estimation I created a column incident, in this column I have to compare if the incident-Createddate  greater or less than the choosed Start date and the same for closed date and End date, to give incident a value from (-1,0 or 1).

      But I can not use the Start and End date from my date tables, the DAX can't see them, it assumes that Start date has multiple values where it expects one value. I defined four variables like this:

       

       

      Incident = Var EskBeginn = ('Incident'[System_CreatedDate])                                                                                        Var EskEnde = ('Incident'[Microsoft_VSTS_Common_ClosedDate])                                                                                        Var StartDtum = SELECTEDVALUE('StartDate'[Date])
Var EndDatum = SELECTEDVALUE('EndDate'[Date])
return IF(EskBeginn < StartDtum && EskEnde > StartDtum && EskEnde < EndDatum, 1, IF(EskBeginn > StartDtum && EskBeginn < EndDatum  && EskEnde > EndDatum, -1, 0))

       

       

      selectedvalue did not help, and I can not make a relationship between the three tables.

       
       
       
       
    • rashamohsen's avatar
      rashamohsen
      Frequent Visitor
      6 years ago

      HI

       

      I am sorry, I tried to send a screenshot but it does not let me send it, I could not load it and I could not copy it.