Measure to check for existence of two values in a column

[Anonymous]

Hi Community,

Following is me sample data set:

I would like to create two measures M1 & M2.

M1 = distinctcount(client id) where status = 'Closed' & reason is both 1 and 3. So M1 resultset looks like:

M2= distinctcount(client id) where status = 'Closed' & reason is NOT both 1 and 3( that is either 1 or 3 but not both 1 & 3). So M2 resultset looks like:

Many Thanks in advance!

[ryan_mayu · Accepted solution]

@Anonymous 

maybe you can create a new table

New table = 
VAR tbl=FILTER('Table (2)',('Table (2)'[Reason]=1 ||'Table (2)'[Reason]=3)&&'Table (2)'[status]="closed")
VAR tbl2=ADDCOLUMNS(ADDCOLUMNS(tbl,"count",countx(FILTER(tbl,'Table (2)'[ClientID]=EARLIER('Table (2)'[ClientID])),'Table (2)'[Reason])),"type",if([count]=1,"M2","M1"))
return SELECTCOLUMNS(tbl2,"category",'Table (2)'[Category],"clientID",'Table (2)'[ClientID],"apnt_id",'Table (2)'[apnt_id],"status",'Table (2)'[status],"reason",'Table (2)'[Reason],"type",[type])