Forum Discussion

Kostas's avatar
Kostas
Icon for Helper IV rankHelper IV
6 years ago
Solved

Single Date filter for Multiple Columns and add Measures

Hello, 

I am currently having an issue that it has to do with both, how to develop functions and how to do the calculations.

I have a table that contains a unique ID and multiple column dates (see print screen). I need in my visual to have a single date filter and the user to be able to see different results within the specified period in the filter. 

The results will always be a DistinctCount ID and then each bar (I am thinking to create a waterfall with different measure values) to have each own calculations.

Example of the calculations may be:

Count of ID -  (“Date1” before start of period) AND ((“Date2” blank) OR (“Date3” after beginning of period))

Count of ID– (“Date2” between start of period and end of period)

Count of ID– (“Date1” between start and end of period)

Count of ID– (“date1” before end of period) AND ((“Date2” blank) OR (“Date3” after end of period))

 

I am not really need all the calculations but I will need from someone to provide me the logic of how to be able to create a single date slicer for all the columns and then how to be able to create measures that will show a count based on multiple parameters according to the selected date.

 

I found multiple articles raised by users in the forum. The best solution that I found was to unpivot the dates and connect it to calendar table but that will not solve the before or after time period or the multiple filter parameters. 

 

Any suggestions?

Need Help
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hi Kostas ,

    Here is the .pbix that you can try it again: PBIX 

    Put the Date filter measure in the table visual filter and set up value as 1, put the Total measure in a card visual direcrtly and it should work fine.

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

12 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Create a single, disconnected date table. Use Measures to get the dates selected by the user and then filter as appropriate.

    • Kostas's avatar
      Kostas
      Icon for Helper IV rankHelper IV
      6 years ago

      Hello amitchandak , 

      thank you for the response and the article is really helpfull. Especialy the use relationship function that I didn't know about.

      I understand that I need to create a date table and place the date value in the filter. 

      Also, I can see how I can take the count within the period that the user will select by connecting the two tables and use the USERELATIONSHIP function to create the measure but, how I will get the values before the period?

      As in example one that I specified before, the perfect solution would be (Dateinfull column = Date column in date calendar table):

       

      Open at Start of Period = CALCULATE(DISTINCTCOUNT([ID]), USERELATIONSHIP([Date1], 'Calendar'[Date in Full]) < 'Calendar'[Date in Full], ISBLANK([Date2]) || USERELATIONSHIP([Date2] , 'Calendar'[Date in Full]) > 'Calendar'[Date in Full])

      But I cannot use the "userelationship" function in that way.
       
      What I could do is to calculate individually all measures and the create the parent measure that will give me the final results but, 
      The issue is on how to return all dates for the specific relationship that occur before or after the calendar date. 
       
      Could you explain me the process and the use please?
       
      Thanks