none
User Select a Date then we bring up valid data from 4 dates if the selected date meet the criteria

    Question

  • Hi,

    I facing some difficulties on a PowerPivot Report, that requires end user to select a date then the Report will return a result if they meet below and logic

    AND(<logical 1: Selected date is within DateStart and DateEnd )>,<logical 2: Selected date is within onTaskFrom and onTaskUntil>)

    AND function:

    =IF(AND(DateStart =<DateEnd ,onTaskFrom>=onTaskUntil), "All true", "One or more false")

    I have created a Calendar table as lookup (added to slicer to make it selectable or the Selected date), on actual data I have 4 dates they are

    1. DateStart
    2. DateEnd
    3. onTaskFrom
    4. onTaskUntil

    I have tried a lot of DAX but could get it work could anyone help on this please.

    Many thanks!



    erkindunya





    Monday, October 14, 2013 3:49 PM

Answers

  • Hi

    Perhaps you could try this approach in your DAX:

    IsValid:=IF (
      HASONEVALUE('Date'[Date]),
      CALCULATE(
        IF([CountOfRows], TRUE, FALSE), 
        ALL('Date'), 
        VALUES('Date'[Date]) >= Data[DateStart], 
        VALUES('Date'[Date]) <= Data[DateEnd], 
        VALUES('Date'[Date]) >= Data[onTaskFrom], 
        VALUES('Date'[Date]) <= Data[onTaskUntil]
      ),
      FALSE
    )

    You would then wrap each of your calculated measures with an IF function and use the above calculated measure to determine whether to return the data or not:

    ValidatedCountOfId:=
    IF(
      [IsValid], 
      [CountOfId], 
      BLANK()
    )

    Let me know whether this does what you're expecting.

    Please note that the dates are in dd/mm/yyyy format.

    Here is test data I have used:

    Id DateStart DateEnd onTaskFrom onTaskUntil
    1 01/01/2012 01/02/2012 01/01/2012 30/01/2012
    2 01/01/2012 01/02/2012 02/02/2012 31/01/2012
    3 02/02/2012 04/02/2012 03/02/2012 05/02/2012
    4 03/02/2012 02/02/2012 03/02/2012 05/05/2012

    This is the outcome:

    Michael





    Monday, October 21, 2013 4:51 PM
    Answerer
  • Hi erkindunya,

    The two measures were defined as

     [CountOfId]:=COUNT('Data'[ID]) 

    (Note: COUNT was used instead of COUNTA based on the assumption that the column only hold integer values. If this is not the case and the column may containt text values then COUNTA is more appropriate)

    and

    [CountOfRows]:=COUNTROWS('Data')

    Since you are using Power Pivot V1, instead of

    HASONEVALUE('Date'[Date])

    you can use

    COUNTROWS(VALUES('Date'[Date])) = 1

    This should give the same behaviour.

    Hope this answers your questions :-)

    Michael


    Friday, October 25, 2013 3:05 PM
    Answerer
  • Hi erkindunya,

    I've spent some time playing about with the totals behaviour and have made some adjustments to the approach.

     

    What you will need to do is instead of using the [IsValid] measure as a flag, you can wrap the various measures in the following DAX directly:

    ValidatedCountOfId:=IF (
      COUNTROWS(VALUES('Date'[Date])) = 1,
      CALCULATE(
        [CountOfId], 
        ALL('Date'), 
        VALUES('Date'[Date]) >= Data[DateStart], 
        VALUES('Date'[Date]) <= Data[DateEnd], 
        VALUES('Date'[Date]) >= Data[onTaskFrom], 
        VALUES('Date'[Date]) <= Data[onTaskUntil]
      ),
     BLANK()
    )

    In the above case, I am ensuring that the [CountOfId] measure is only taken into consideration when all of the date criteria is true.

    If there was another measure called [Amount], for example, the validated version of it would be defined as:

    ValidatedAmount:=IF (
      COUNTROWS(VALUES('Date'[Date])) = 1,
      CALCULATE(
        [Amount], 
        ALL('Date'), 
        VALUES('Date'[Date]) >= Data[DateStart], 
        VALUES('Date'[Date]) <= Data[DateEnd], 
        VALUES('Date'[Date]) >= Data[onTaskFrom], 
        VALUES('Date'[Date]) <= Data[onTaskUntil]
      ),
     BLANK()
    )

    This should give the desired effect and the totals should also only include the valid data.

    Hope this helps.

    Michael


    Monday, October 28, 2013 11:01 PM
    Answerer

All replies

  • Hi

    Perhaps you could try this approach in your DAX:

    IsValid:=IF (
      HASONEVALUE('Date'[Date]),
      CALCULATE(
        IF([CountOfRows], TRUE, FALSE), 
        ALL('Date'), 
        VALUES('Date'[Date]) >= Data[DateStart], 
        VALUES('Date'[Date]) <= Data[DateEnd], 
        VALUES('Date'[Date]) >= Data[onTaskFrom], 
        VALUES('Date'[Date]) <= Data[onTaskUntil]
      ),
      FALSE
    )

    You would then wrap each of your calculated measures with an IF function and use the above calculated measure to determine whether to return the data or not:

    ValidatedCountOfId:=
    IF(
      [IsValid], 
      [CountOfId], 
      BLANK()
    )

    Let me know whether this does what you're expecting.

    Please note that the dates are in dd/mm/yyyy format.

    Here is test data I have used:

    Id DateStart DateEnd onTaskFrom onTaskUntil
    1 01/01/2012 01/02/2012 01/01/2012 30/01/2012
    2 01/01/2012 01/02/2012 02/02/2012 31/01/2012
    3 02/02/2012 04/02/2012 03/02/2012 05/02/2012
    4 03/02/2012 02/02/2012 03/02/2012 05/05/2012

    This is the outcome:

    Michael





    Monday, October 21, 2013 4:51 PM
    Answerer
  • Hi Michael,

    Thank you very much for your kindly help, I am using PowerPivot v1 with SQL 2008 R2, the "hasonevalue" is available on SQL 2012 version. On your solution you have created few calculated column such as [CountOfId] and [CountOfRows].

    Did you created the above calulated columns by  [CountOfId]=COUNTA('Data'[ID]) and [CountOfRows]==COUNTROWS('Data')?

    I have upload the "Report data" by removing the Calender lookup table and used DateStart as Date column. Many thanks in advance!


    erkindunya

    Friday, October 25, 2013 1:48 PM
  • Hi erkindunya,

    The two measures were defined as

     [CountOfId]:=COUNT('Data'[ID]) 

    (Note: COUNT was used instead of COUNTA based on the assumption that the column only hold integer values. If this is not the case and the column may containt text values then COUNTA is more appropriate)

    and

    [CountOfRows]:=COUNTROWS('Data')

    Since you are using Power Pivot V1, instead of

    HASONEVALUE('Date'[Date])

    you can use

    COUNTROWS(VALUES('Date'[Date])) = 1

    This should give the same behaviour.

    Hope this answers your questions :-)

    Michael


    Friday, October 25, 2013 3:05 PM
    Answerer
  • Hi Michael,

    I got it work with your help, one more question the total includes the False value how can we exclude from it? see below image it should give 518 for ture because it has 18 false there.

    Many thanks again!

     


    erkindunya

    Monday, October 28, 2013 12:07 PM
  • Hi erkindunya,

    I've spent some time playing about with the totals behaviour and have made some adjustments to the approach.

     

    What you will need to do is instead of using the [IsValid] measure as a flag, you can wrap the various measures in the following DAX directly:

    ValidatedCountOfId:=IF (
      COUNTROWS(VALUES('Date'[Date])) = 1,
      CALCULATE(
        [CountOfId], 
        ALL('Date'), 
        VALUES('Date'[Date]) >= Data[DateStart], 
        VALUES('Date'[Date]) <= Data[DateEnd], 
        VALUES('Date'[Date]) >= Data[onTaskFrom], 
        VALUES('Date'[Date]) <= Data[onTaskUntil]
      ),
     BLANK()
    )

    In the above case, I am ensuring that the [CountOfId] measure is only taken into consideration when all of the date criteria is true.

    If there was another measure called [Amount], for example, the validated version of it would be defined as:

    ValidatedAmount:=IF (
      COUNTROWS(VALUES('Date'[Date])) = 1,
      CALCULATE(
        [Amount], 
        ALL('Date'), 
        VALUES('Date'[Date]) >= Data[DateStart], 
        VALUES('Date'[Date]) <= Data[DateEnd], 
        VALUES('Date'[Date]) >= Data[onTaskFrom], 
        VALUES('Date'[Date]) <= Data[onTaskUntil]
      ),
     BLANK()
    )

    This should give the desired effect and the totals should also only include the valid data.

    Hope this helps.

    Michael


    Monday, October 28, 2013 11:01 PM
    Answerer
  • Hi Michael,

    It is very kind of you I was doing test till now, looks cool.

    Regards

    Erkindunya


    erkindunya

    Wednesday, October 30, 2013 2:31 PM