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 ...
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.

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

 

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Kostas , Usage is wrong. Can you share sample in table format and expected output.

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

    Hello amitchandak 

    Please find attached the table (it is the same as above). 

    I need to perform the calculations explained above and place them into a visual or individual cards. 

    The users need to be able to have a single date filter and to see the distinct count of ID's within the period that they will select.

    The first example that mentioned is that I will need the distinct count of ID's that will meet two criteria:

    1) The dates in the Date 1 column will be before the selected period (in the filter)

    2) The dates in the Date 2 column will be either after the selected period or blank.

     

    For Example if the user select the period 30 May to June 10 2020 the count should be 1 (ID = 4294)

     

    I hope that make sense

    IDDate1Date2
    429405 May 202004 June 2020
    339230 May 202009 June 2020
    421731 December 202010 January 2021
    421631 December 202010 January 2021
    421531 August 202010 September 2020
    421431 August 202010 September 2020
    421330 September 202010 October 2020
    421731 December 202010 January 2021
    421631 December 202010 January 2021

     

    Thanks in advance


    Kostas

    • v-yingjl's avatar
      v-yingjl
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi Kostas ,

      Based on your latest update, if I got it correctly, you can create a control measure like this and set the value as 1 to put it in the filter of the table visual:

      Date filter =
VAR _mindate =
    CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
VAR _maxdate =
    CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
VAR _date1 =
    SELECTEDVALUE ( 'Table'[Date1] )
VAR _date2 =
    SELECTEDVALUE ( 'Table'[Date2] )
RETURN
    IF ( _date1 < _mindate && _date2 < _maxdate, 1, 0 )

      If you want to count, just create a slimar measure and put it in the table visual:

      Count =
VAR _mindate =
    CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
VAR _maxdate =
    CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
VAR _date1 =
    SELECTEDVALUE ( 'Table'[Date1] )
VAR _date2 =
    SELECTEDVALUE ( 'Table'[Date2] )
RETURN
    SUMX ( 'Table', IF ( _date1 < _mindate && _date2 < _maxdate, 1, 0 ) )

      The result is like this:

      Here is the sample file hopes to help you solve this issue, please try it: PBIX 

       

      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.

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

        hello v-yingjl 

        I can see the logic behind your code and thanks for the help. 

        Unfortunately when I am applying the date measure in the filter section as a way for the filter to show only the values with "1" it automatically only shows to be the advance filter and I cannot apply any values inside the box. So I cannot test it. 

        Any ideas why it does not let me apply any filters? When I also tried to place the measure into a slicer it did not let me to.

         

        If you believe that is an application issue I will accept the solution as it seems that should work.

         

         

        Thanks 

        Kostas