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