cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Search for last date with certain condition

Hello, I'm Agustin lopez, Horticulture from Chile working with avocado. I'm working with some irrigation data set anda  I need a hand with a DAX.

I have 2 tables.
1- The theorical data table that is supouse what has to happend. (Name: Base de dato Agricola)
2- The data table with the data that really happend. (Name: Informacion Agricola)

I have the following data set

Table 1 (theorical irrigation)
Sector     Time of irrigation(min)
5                 80
6                100

Table 2 (Real Irrigation)
Sector      Date         Time of irrigation
5           20-11-2021       80
5            22-11-2021     160
5             01-12-2021     80

In the dax, I need to see when was the last day (max date) of an irrigation that has a 2x time of irrigation from the Theorical data (Table 1). i.e. the dax should give me back the date of 22-11-2021  of the data set as example. I use as a key the Sector.

Thanks!

1 ACCEPTED SOLUTION
Super User

Try this:

``````max date of an irrigation =
VAR _A =
MAX( 'theorical irrigation'[Time of irrigation] )
VAR _B =
FILTER(
'Real Irrigation',
"TI", 'Real Irrigation'[Time of irrigation] / _A
),
[TI] >= 2
)
RETURN
MAXX( _B, [Date] )``````

I used this table as a Real Irrigation (Add some data for Sector 6):

and the Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

4 REPLIES 4
Super User

Try this:

``````max date of an irrigation =
VAR _A =
MAX( 'theorical irrigation'[Time of irrigation] )
VAR _B =
FILTER(
'Real Irrigation',
"TI", 'Real Irrigation'[Time of irrigation] / _A
),
[TI] >= 2
)
RETURN
MAXX( _B, [Date] )``````

I used this table as a Real Irrigation (Add some data for Sector 6):

and the Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Regular Visitor

@VahidDM thanks for your solution, It's work really well tell me when you come to chile to give u some avocados hahaha

Can i ask you one last question.
If know I want to count how many of those events of 2x irrigation happend how can I do it?

Same data set.

Thanks

Super User

Chile is in my travel list now 🙂

Try this to count how many of those events of 2x irrigation happend :

``````max date of an irrigation =
VAR _A =
MAX( 'theorical irrigation'[Time of irrigation] )
VAR _B =
FILTER(
'Real Irrigation',
"TI", 'Real Irrigation'[Time of irrigation] / _A
),
[TI] >= 2
)
RETURN
COUNTROWS( _B )``````

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Super User

Hi,

This should do what you are describing:

Irrigation Maxdate =
var selection = SELECTEDVALUE('Irrigation T'[Irrigation])
var sector = MAX(Irrigation[Sector]) return
MAXX(filter(Irrigation,Irrigation[Irrigatio]=2*selection&&Irrigation[Sector]=sector),Irrigation[date])

I hope this helps and if it does consider accepting this as a solution and giving the post a thumbs up!

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.