Calculate the number of rows using SEARCH and FILTER

[MatthewFiero]

Hello Power BI Community,

I am relatively new to Power BI. I'm about 3 weeks in now and learning fast. What an amazing tool! 

I'm trying to create a measure to count the number of instances with two conditions. One condition is that it containts the word "PENDING" in one column and prior to 2019 for the date.

This is what I have:

WIP = CALCULATE(COUNTROWS(FAST),SEARCH("PENDING",FAST[Claim Status],,0), CALCULATE(COUNTROWS(FAST),FAST[Create Date] < DATE(2019,01,01)))

Separetely these two filters work but together I am getting the error  that CALCULATE has been used as a TRUE/FALSE statement and I don't understand exactly what that means. 

Is anyone able to help me work through this?

Thank you for your time and thank you for this wonderful community! 

Matthew

[Fowmy · Accepted solution]

@MatthewFiero 

Try it this way:

WIP = 

CALCULATE(
    COUNTROWS(FAST),
    FILTER(
        FAST,
        FAST[Claim Status] = "PENDING" && FAST[Create Date] < DATE(2019,01,01)
    )
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[MatthewFiero]

Hi @Fowmy ,

Thank you for such a quick response. What if the claim status only contains the word "PENDING"? In my case there are multiple different pending status. 

I had no idea you could use the && function like that which should be very helpful in the future!

Thank you,
Matthew

[Fowmy]

@MatthewFiero 
The DAX that I shared will only take into account if the value is only "Pending".

Hope the solution works for you

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn 

[MatthewFiero]

Looks like this may work except I have 1 extra value for some reason (I'll have to check the data)

WIP =

CALCULATE(

COUNTROWS(FAST),

FILTER(

FAST,

SEARCH("PENDING",FAST[Claim Status],,0) && FAST[Create Date] < DATE(2019,01,01)

)

)