Forum Discussion

Creative_tree88's avatar
Creative_tree88
Helper V
2 years ago
Solved

Finding a value between two dates

Hi all - I need to find a value, most likely using lookup function??  Not sure.  But the value is between a range, consisting of two dates.  However, the key I'm using to lookup, has multiple values ...
Need Help
  • BIswajit_Das's avatar
    BIswajit_Das
    2 years ago

    Hello Creative_tree88

    Try this DAX out
    (Here 'Table1' is 'Data' & 'Table2' is 'Lookup Data')

    #EXAM2 =
    VAR _start = 'Table1'[Admission]
    VAR _end = 'Table1'[Discharge]
    VAR _key = 'Table1'[KEY]

    VAR _singleDateExam =
        LOOKUPVALUE(
            'Table2'[Exam],
            'Table2'[Event Date].[Date], _start,
            'Table2'[KEY], _key
        )

    VAR _multiDateExams =
        CALCULATE(
            CONCATENATEX(
                FILTER(
                    'Table2',
                    'Table2'[Event Date].[Date] >= _start &&
                    'Table2'[Event Date].[Date] <= _end &&
                    'Table2'[KEY] = _key
                ),
                'Table2'[Exam],
                ", "
            )
        )

    RETURN
    IF(
        ISBLANK(_end),
        _singleDateExam,
        _multiDateExams
    )
    If you found this information useful, please consider marking it as the accepted solution.
    Thanks & Regrads...

BIswajit_Das's avatar
BIswajit_Das
Impactful Individual
2 years ago

Hello Creative_tree88

Try this DAX out
(Here 'Table1' is 'Data' & 'Table2' is 'Lookup Data')

#EXAM2 =
VAR _start = 'Table1'[Admission]
VAR _end = 'Table1'[Discharge]
VAR _key = 'Table1'[KEY]

VAR _singleDateExam =
    LOOKUPVALUE(
        'Table2'[Exam],
        'Table2'[Event Date].[Date], _start,
        'Table2'[KEY], _key
    )

VAR _multiDateExams =
    CALCULATE(
        CONCATENATEX(
            FILTER(
                'Table2',
                'Table2'[Event Date].[Date] >= _start &&
                'Table2'[Event Date].[Date] <= _end &&
                'Table2'[KEY] = _key
            ),
            'Table2'[Exam],
            ", "
        )
    )

RETURN
IF(
    ISBLANK(_end),
    _singleDateExam,
    _multiDateExams
)
If you found this information useful, please consider marking it as the accepted solution.
Thanks & Regrads...

  • Creative_tree88's avatar
    Creative_tree88
    Helper V
    1 year ago

    BIswajit_Das - I've just tried to use this same method on an updated table, based exactly on the previous one, but it's now saying 'ERROR - A table of multiple values was supplied where a single value was expected'

    I really do not understand as I have not changed anything at all...any advice at all?   Thank you.

    • BIswajit_Das's avatar
      BIswajit_Das
      Impactful Individual
      1 year ago

      Hello Creative_tree88 
      I think you're creating a measure/table in which you are returning a column of multiple values.


      "ERROR - A table of multiple values was supplied where a single value was expected" this error
      occurs in DAX when a function expects a single value but instead receives a table or multiple values.

       

      Create a calculated column and It Suppose to work
      If it does not work then attach the demo data i'll try to fix the DAX

      Thanks & Regards...

      • Creative_tree88's avatar
        Creative_tree88
        Helper V
        1 year ago

        BIswajit_Das I added a new column and copied the DAX into the formula bar at the top.  Did it the same way as previously.  For some reason it's not working unlike the previous month when it worked perfectly.  I'm trying to understand if I have done something differently to last time...but so far am a bit confused as to why all of a sudden it's stopped working...

         

        I'll send you some demo data.

  • Creative_tree88's avatar
    Creative_tree88
    Helper V
    1 year ago

    BIswajit_Das I'm now getting a message on this:

    Column reference to 'Event Date' in table 'Activity' cannot be used with a variation 'Date' because it does not have any.

    Any idea why this would be happening?  Obviously now returning error in the column.  Many thanks!