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 

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

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

      Hi Kostas ,

      Have you tried my sample file attached previously? This measure will work fine in the table visual. However you cannot put it in a card visual filter. Even you put it in the filter, you cannot set its value as 0 or 1 in the card visual. The card visual is not supported to put this control measure in it currently.

       

      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

        Hey v-yingjl ,

        I copied paste your measures into my pbix report. 

        I placed the open in period measure as you did into the table and tried to apply filter on the table the date measure. 

        When I am placing the date measure as filter I cannot change anything in the filtering or place the number ( 1 as you did).

        See print screen. 

         

        I just re-installed to my machine the Power BI bu still nothing. 

         

        Thanks