Get value from another table when a date is between two others

[Anonymous]

Hi everybody,

I am facing a problem to get informations from a table to another.

I am dealing with servers alerts, and I have to create a report listing alerts witch are to be excludes due to specifics events.

I have two tables witch are not linked :

The first table (named Exclusions) contains 3 columns :

- the name of the exclusion period

- the start date of the exclusion period (format date/time)
- and the end date of the exclusion period (format date/time)

The 2nd table (Alerts) contains, among other data, a column "date".

I am trying to determine, for each line of my Alerts table, if the alert is within an exclusion period (define by table Exclusions)

base on Solved: Determine if date is between 2 dates - Microsoft Power BI Community I created a calculated column in my table Alerts. It looks like :

exclusion_name =

CALCULATE(

VALUES( Exclusions[name]),

FILTER(

Exclusions,

Exclusions[startdate] <= EARLIER( Alerts[date])

&& Exclusions[enddate] >= EARLIER(Alerts[date]

) ) )

but I get an error message like : "a table of multiple values ​​was provided while a single value was expected"

I just don't understand why because my Exclusions table only contains 4 exclusions periods, and each is with a unique name.

Thank for reading, thanks for helping 🙂

Regards

[Ashish_Mathur · Accepted solution]

Hi,

Replace VALUES() with MAX().  If it does not help, then share the link from where i can download your PBI file.

[Anonymous]

Hi Ashis_Mathur,

I thank you for your help.

Trying with MAX() doesn't work because a value is always returned even when the date of the alert isn't in a exclusion period.
How can I share my report ? is there a specific share for the community or I can put it on a custom url ?

[Ashish_Mathur]

Hi,

Upload the file to Google Drive and share the download link here.

[Anonymous]

Hi,

Here is the link to my report : 

https://drive.google.com/file/d/1t3iKCDWPo_eTKbjdoyb43s0VtCN1mF8W/view?usp=sharing

regards

[Ashish_Mathur]

Hi,

This works absolutely fine

ExclusionName = CALCULATE(max( Exclusions[Dysfonctionnement]),FILTER(Exclusions,Exclusions[Heure début de l'exclusion] <= EARLIER( Alerts[Alert_date])&& Exclusions[Heure fin de l'exclusion] >= EARLIER(Alerts[Alert_date])))

This is exactly what i had suggested to you even earlier.

[Anonymous]

Hi Ashish_Mathur,

You are right it works fine.
When I test I didn't expected to get so much exclusion and thought that the last exclusion period was taken by default : but as I have an exclusion from 1st to 31st august it is normal (

I have to apply some filters).

Once again thank you for your help, and my excuse for the little lost of time 😥

Problem solved 👍

[Ashish_Mathur]

You are welcome.