Forum Discussion

JamesMF1982's avatar
JamesMF1982
Frequent Visitor
3 years ago

Return field text when date condition is met

Hi,

 

I am trying to pull the postcodes when a particular date condition is met, i.e. a enquiry was started within the selected month. 

 

I've tried the following:

Measure = calculate(values(PROSPECTS[ProspectPostcode]),USERELATIONSHIP('CALENDAR'[CalendarDate],ENQUIRYDETAILS[EnquiryBecameLeadDate]))

 

but it errors stating "A table with multiple values was supplied where a single value was expected". I know this isn't the right expression, but I am stumped.

 

Any help would be great.

 

Thanks


JAmes

Need Help

2 Replies

  • selimovd's avatar
    selimovd
    Icon for Most Valuable Professional rankMost Valuable Professional
    3 years ago

    Hey  , 

    what exactly should the result be?

    You return "VALUES( PROSPECTS[ProspectPostcode] )". Values returns a table with the distinct values.

     

    What do you expect the result to look like when you return a whole table? Try some kind of aggregation like:

     

    Measure =
CALCULATE (
    MAX ( PROSPECTS[ProspectPostcode] ),
    USERELATIONSHIP ( 'CALENDAR'[CalendarDate], ENQUIRYDETAILS[EnquiryBecameLeadDate] )
)

     

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

    Best regards
    Denis

    Blog: WhatTheFact.bi
    Follow me:     twitter.com/DenSelimovic

    • JamesMF1982's avatar
      JamesMF1982
      Frequent Visitor
      3 years ago

      Hi, 

      Thanks for getting back to me. What I want to have is a table which just has the postcodes when the lead date was within the selected calendar period. So this might return a table of 30 rows, or 60 depending on the month selected.