Counting rows based on the unique State

[Anonymous]

Hi All,

I am facing issue in creating a measure. I am using Live connection (SSAS cube ) and my table structure is as follows, 

I need to count the distinct number of "Objects" which has only State 2 from the table structured as below.

Note : Objects which has both  States ( State 1 & State 2) should be excluded from the count. 

Objects	State
Obj1	State1
Obj1	State2
Obj2	State2
Obj3	State1
Obj3	State1
Obj3	State2

The count should return "1" as Obj2 is the only value containing only State2. 

Kindly help me getting the requested calcuation in measure. 

Any help would be appreciated. 

Thanks in Advance.

Kindly help - @amitchandak @parry2k @Cmcmahan 

[harshnathani · Accepted solution]

HI @Anonymous ,

You can try this

Measure 6 = 
var a = CALCULATETABLE(VALUES('Table'[Objects]) , 'Table'[State] = "State1")
var b = CALCULATETABLE(VALUES('Table'[Objects]), 'Table'[State] = "State2")
RETURN
COUNTROWS(EXCEPT(b,a))

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi @harshnathani 

Thanks alot. This solution worked. 

I was trying with select columns including othe filters and was getting some values missed in except. Using calculate tables included that filters with ease.

Thanks again. 

Praveen