How to find the user as per their type ??

[Anonymous]

Hello,

Hope you are doing well.

in the below screenshot how to write the DAX query to find ...

1> How many [account Id] are only owner (ans =  1, ie a1)

2> How many [account Id] are only user (ans =  1, ie c3)

3> How many [account Id] are only user and owner (ans =  1, ie b2)

note : all the a1 are having type owner. c3 is having type user. b2 is having both user and owner.

Hope this will help. Hope this question make sence. Incase don't, then just let me know.

[PaulDBrown · Accepted solution]

1) Owners =

VAR _Owner = CALCULATETABLE(VALUES(Table[Account ID), Table[Type] = "Owner")

VAR _User = CALCULATETABLE(VALUES(Table[Account ID), Table[Type] = "User")

RETURN

COUNTROWS(EXCEPT(_Owner, _User))

2) User =

VAR _Owner = CALCULATETABLE(VALUES(Table[Account ID), Table[Type] = "Owner")

VAR _User = CALCULATETABLE(VALUES(Table[Account ID), Table[Type] = "User")

RETURN

COUNTROWS(EXCEPT(_User, _Owner))

3) both=

VAR _Owner = CALCULATETABLE(VALUES(Table[Account ID), Table[Type] = "Owner")

VAR _User = CALCULATETABLE(VALUES(Table[Account ID), Table[Type] = "User")

RETURN

COUNTROWS(INTERSECT(_Owner, _User))

Create 3 table visuals with the Table[Account[ID] field and add each measure to each table visual (1 measure per table) and set the Value as equal to 1

[Anonymous]

Thank you Paul. It works flawless.

Regards,

Kunal

[Anonymous]

@Seanan Hi,

Thanks for the respond.

no for the above senario it would be 1.

i have modified the senario. here i have insert d4 and e5.

1> How many [account Id] are only owner (ans =  1, ie a1,d4,e5)

2> How many [account Id] are only user (ans =  1, ie c3)

3> How many [account Id] are only user and owner (ans =  1, ie b2)

similarly i can add users too. Hope you got the point.

[Seanan]

Hi @Anonymous 

I'm a little bit confused by the outcome you are looking for here. For all 3 of the measures you'd like you said the answer = 1, and that all a1 are owner, c3 are type and b2 are both. Wouldn't this mean that your outcome would always be 1?