Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Countifs equivalent matching variable criteria

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;

 ABC
1DateNew PRHostOrderId
28/09/2021TRUEPldOrd/16877001//
39/09/2021FALSEPldOrd/16877001//
410/09/2021FALSEPldOrd/16877001//
511/09/2021FALSEPldOrd/16877001//
612/09/2021FALSEPldOrd/16877001//
713/09/2021FALSEPldOrd/16877001//
814/09/2021FALSEPldOrd/16877001//
98/09/2021TRUEPldOrd/21105414//
109/09/2021FALSEPldOrd/21105414//
1110/09/2021FALSEPldOrd/21105414//
1211/09/2021FALSEPldOrd/21105414//
1312/09/2021FALSEPldOrd/21105414//
1413/09/2021FALSEPldOrd/21105414//
1514/09/2021FALSEPldOrd/21105414//

 

Thanks in advance for any advice offered 🙂

 

Regards

Andrew

Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    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

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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

  • TheoC's avatar
    TheoC
    Community Champion
    4 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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

    • TheoC's avatar
      TheoC
      Community Champion
      4 years ago

      A pleasure Anonymous! All the best 🙂