Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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!
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!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |