Distinct Count with filters and exclusion of certain values

[Anonymous]

i was working in a measure which takes distinct count of id when status is either 5 or 6. I need to exclude ID which has flag as Y for ID. Eg-From the screenshot below 100 has status 5 and Flag as Y so it should be excluded from count. (total count should be 3).

measure= calculate (distinctcount(FI_Table[id]), FI_Table(status) in {5,6} && FI_Table[flag] <>"Y").

this measure seems to be not working.

Input tableOutput

[Anonymous]

Hi @Samarth_18 ,this worked 
Thank you.

[tamerj1]

Hi @Anonymous 
Check sample file https://www.dropbox.com/t/kSgJ3giWijfZE60V
For card viasual you can use 

Count = 
VAR FlaggedIDs = 
    DISTINCT ( 
        SELECTCOLUMNS (
            FILTER ( FI_Table, FI_Table[Flag] = "Y" ),
            "@ID", FI_Table[ID]
        )
    )
VAR AllIDs =
    VALUES ( FI_Table[ID] )
RETURN
    COUNTROWS ( EXCEPT( AllIDs, FlaggedIDs ) )

[Samarth_18 · Accepted solution]

Hi @Anonymous ,

Create a column as below:-

Column = 
var if_Y = COUNTROWS(FILTER('Table (2)','Table (2)'[ID] = EARLIER('Table (2)'[ID]) && 'Table (2)'[Flag] = "Y" ))
var req_status = COUNTROWS(FILTER('Table (2)',('Table (2)'[Status] in {"5","6"} && 'Table (2)'[ID] = EARLIER('Table (2)'[ID]) )))
return  if(if_Y = 0 && req_status > 0,1,0)

and now create a measure as below:-

Measure = calculate(DISTINCTCOUNT('Table (2)'[ID]),'Table (2)'[Column] = 1)

Thanks,

Samarth