Countifs equivalent matching variable criteria

[Anonymous]

Hi, I've had a search and found some countifs solutions for powerBI but none that I can see that are a solution to my  goal so I'm hoping that somone could share a possible solution.

Summary: I need to flag rows true or false if they are new based on a variable criteria match from 2 columns.

The Excel formula I use is quite simple;

=COUNTIFS(A:A,"<"&A2,C:C,C2)=0

So if there is a row found where the Date in column A is less than the current row date and the HostOrderID is the same as the current row HostOrderID the record is not new as it appears in previous rows (count is > 0) and is flagged FALSE otherwise it's new (count = 0) and flagged TRUE.

I have tried this but it returns true for all rows.

Example data set;

	A	B	C
1	Date	New PR	HostOrderId
2	8/09/2021	TRUE	PldOrd/16877001//
3	9/09/2021	FALSE	PldOrd/16877001//
4	10/09/2021	FALSE	PldOrd/16877001//
5	11/09/2021	FALSE	PldOrd/16877001//
6	12/09/2021	FALSE	PldOrd/16877001//
7	13/09/2021	FALSE	PldOrd/16877001//
8	14/09/2021	FALSE	PldOrd/16877001//
9	8/09/2021	TRUE	PldOrd/21105414//
10	9/09/2021	FALSE	PldOrd/21105414//
11	10/09/2021	FALSE	PldOrd/21105414//
12	11/09/2021	FALSE	PldOrd/21105414//
13	12/09/2021	FALSE	PldOrd/21105414//
14	13/09/2021	FALSE	PldOrd/21105414//
15	14/09/2021	FALSE	PldOrd/21105414//

Thanks in advance for any advice offered 🙂

Regards

Andrew

[Anonymous]

Thanks @amitchandak & @TheoC  for your prompt responses. Your help is much appreciated !!

[TheoC]

A pleasure @Anonymous! All the best 🙂

[TheoC]

Hi @Anonymous 

You can use something such as the following calculated column. 

col_Date = 

VAR _CurrentHost = 'Table'[HostOrderId]
VAR _Date = 'Table'[Date]
VAR _FirstDate = MIN ( 'Table'[Date] )
VAR _CountDate = COUNTROWS ( FILTER ( ALL ( 'Table' ) , _Date = 'Table'[Date] ) )
VAR _CountHost = COUNTROWS ( FILTER ( ALL ( 'Table' ) , _CurrentHost = 'Table'[HostOrderId] ) )

RETURN 

IF ( _CountDate > 1 && _CountHost > 1 && _Date = _FirstDate , TRUE , FALSE )

Hope it helps 🙂

Theo

PS - my bad, after I posted this I noticed @amitchandak had a response as well. I had it in draft for a bit. Apologies!  Please give the solution to @amitchandak if his gives you what you want.

[amitchandak · Accepted solution]

@Anonymous , a new column like

if([Date] < minx(filter(Table,[HostOrderId]  =earlier([HostOrderId]) ),[Date]) , True(), false() )

or

if([Date]<= minx(filter(Table,[HostOrderId]  =earlier([HostOrderId]) ),[Date]) , True(), false() )

Change as per need