MINIF Excel Function in DAX, Unrelated Tables

[Anonymous]

I am trying to move my Excel function into DAX. The two tables mentioned are unrelated. 

First, the Excel formula:

=MINIFS(Table4[Closed By Cut-off],Table4[Logged By Cut-off],">"&[@[Date Logged]],Table4[Date Type],"="&[@Variable])

The goal is to return the MIN value from Table4 Column Closed by Cutoff where the dates in Table4 Column Logged By Cutoff are > the Date Logged in my raw data set AND where the rows in Table4 Column Data Type = Variable in the raw data set.

In Power BI there are two tables: Raw Data & Schedule. There are no connections between the two tables. 

Is there a formula that could help me out here? Its possible for me to break up Schedule into multiple tables to remove the AND condition. I will just need to add multiple columns. The most critical section is the bolded portion of the Excel formula.

[Anonymous]

The raw data is an extract from a ticketing system. Date Logged in the raw data is the date a user logged the ticket. The MINIFs function with the Schedule table pulls the Target Close Date. When then compare the Ticket Close date versus the Target Date. 

Raw Data

Date Logged	Variable	Closed by Cut-off
10/3/20 7:03 AM	REG	10/20/2020 5:00
10/2/20 7:36 AM	STDIN	10/9/2020 5:00
10/2/20 9:47 AM	STDIN	10/9/2020 5:00

Schedule 

Date Type	Target Date	Logged By Cutoff	Closed By Cutoff
STDIN	9/30/20 5:00 AM	9/28/20 5:00 AM	10/1/20 5:00 AM
STDIN	10/8/20 5:00 AM	10/6/20 5:00 AM	10/9/20 5:00 AM
REG	9/30/20 5:00 AM	9/28/20 5:00 AM	10/1/20 5:00 AM
REG	10/19/20 5:00 AM	10/17/20 5:00 AM	10/20/20 5:00 AM

[v-yiruan-msft]

Hi @Anonymous ,

You can create a measure as below:

Measure = 
VAR _sdatetype=MAX('Schedule'[Date Type])
VAR _rlogdate=CALCULATE(MAX('Raw Data'[Date Logged]),FILTER('Raw Data','Raw Data'[Variable]=_sdatetype))
RETURN
 CALCULATE(MIN('Schedule'[Closed By Cutoff]),FILTER('Schedule','Schedule'[Date Type]=_sdatetype&&'Schedule'[Logged By Cutoff]>_rlogdate))

Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

[Anonymous]

Thank you @v-yiruan-msft ! This does look like it works as a Measure but not as an added column.

Do you know a way where I can add a new column to the data set with a formula to grab the same result?

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

You can create a calculated column as below:

Column = 
VAR _rlogdate=CALCULATE(MAX('Raw Data'[Date Logged]),FILTER('Raw Data','Raw Data'[Variable]='Schedule'[Date Type]))
RETURN
 CALCULATE(MIN('Schedule'[Closed By Cutoff]),FILTER('Schedule','Schedule'[Date Type]=EARLIER('Schedule'[Date Type])&&'Schedule'[Logged By Cutoff]>_rlogdate))

Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

[BA_Pete]

Hi @Anonymous ,

Can you provide a sample of your Raw Data and Schedule table data please, and also explain how both of these relate to Table4 in your example calculation?

Thanks,

Pete