Need help to convert an Excel formula into PowerBI

[spaceuser]

Just started with PowerBI. Need to following Excel formula to measure different short codes in PowerBI. 

=COUNTIF(A2;"*AI*")

=COUNTIF(A2;"*AR*")

EXCEPTION_NAME    AI   AR

AI,LS,MP,UPL,AR        1      1

AI,LS,MP                    1      0

CW,AR                       0      1

[v-cherch-msft · Accepted solution]

Hi @spaceuser

You may refer to below measure.

AI = 
CALCULATE (
    COUNT ( Table1[EXCEPTION_NAME] ),
    FILTER ( Table1, SEARCH ( "AI", Table1[EXCEPTION_NAME], 1, 0 ) > 0 )
)
    + 0

AR = CALCULATE(COUNT(Table1[EXCEPTION_NAME]),FILTER(Table1,SEARCH("AR",Table1[EXCEPTION_NAME],1,0)>0))+0

Regards,

Cherie

[Greg_Deckler]

Several ways to do this, one way:

Measure AI = COUNTX(FILTER('Table',[AI]=1),[EXCEPTION_NAME])

Measure AR = COUNTX(FILTER('Table',[AR]=1),[EXCEPTION_NAME])