count rows

[Anonymous]

Dear Friends

i have table with thousands of rows as shown:

 i need to count the number of "MR Number" wherer MR number contains only Status = "Initial", for example in above MR 1322398 & 1321621 have only status ="Initial", so that MR number Count=2

how can i do this by dax measure for all rows

Thanks

[smpa01]

@Anonymous 

Measure =
CALCULATE (
    COUNT ( tbl[Status] ),
    FILTER ( VALUES ( tbl[Status] ), tbl[Status] = "INITIAL" )
)

[mh2587]

a=Calculate(count([MR Number]),Filter(TableName,TableName[Status] = "Initial"))

[Pragati11]

HI @Anonymous ,

You can simply create a measure:

newCacl =
CALCULATE (
    DISTINCTCOUNT ( yourTablname[MR Number] ),
    yourTablname[Status] = "INITIAL"
)

In the above DAX just replace with your table name and column names.

Thanks,

Pragati

[smpa01]

@Pragati11  in reponse to the previous

[Pragati11]

Hi @smpa01 ,

I think you updated you response from the initial one. This will definitely work.

Thanks,

Pragati

[amitchandak · Accepted solution]

@Anonymous , Try a new measure like

countx(filter(summarize(Table, Table[MR Number], "_cnt", count(Table[MR Number]), "_init", countx(filter(Table, Table[Status] = "Initial"),Table[MR Number])), [_cnt] =[_init]),[MR Number])