Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 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 n...
Need Help
  • amitchandak's avatar
    amitchandak
    5 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
Icon for Community Champion rankCommunity Champion
5 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.