Dax Calculation for distinct Values

[Anonymous]

Hi I am looking to achieve below  output . Can someone help on how to do this in DAX.

Account	Interaction Type
1	call
1	inperson
2	call
3	inperson
3	call
3	msg

looking for formula to achieve  count where interaction type is only call ie   Account count=1 ( account id 2)

only msg account count= 0

only inperson account count=0

multiple interaction account type = 2 (account id 1 and 3 )

Appreciate your help !!

Raju

[v-lionel-msft]

Hi @Anonymous ,

Please refer to my .pbix file.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@Anonymous ,

only Call =
countx(filter(summarize(Table, Table[Account], "_1", count(Table[Interaction Type])
, "_2",calculate(count(Table[Interaction Type]), filter(Table, Table[Interaction Type]= "Call"))),
[_1] =[_2]) ,[Account] )

only inperson=
countx(filter(summarize(Table, Table[Account], "_1", count(Table[Interaction Type])
, "_2",calculate(count(Table[Interaction Type]), filter(Table, Table[Interaction Type]= "inperson"))),
[_1] =[_2]) ,[Account] )

only msg=
countx(filter(summarize(Table, Table[Account], "_1", count(Table[Interaction Type])
, "_2",calculate(count(Table[Interaction Type]), filter(Table, Table[Interaction Type]= "msg"))),
[_1] =[_2]) ,[Account] )

Mutiple Interactions =
countx(filter(summarize(Table, Table[Account], "_1", distinctcount(Table[Interaction Type])),
[_1] >=2) ,[Account] )

[Anonymous]

Hi Amit,

there is one more scenario to add to this.  if the interaction Type is blank then we should not be counting those records . but the above formula is counting in all individual counts(only Call,only msg,only inperson) which is bit off.  could you please help how to avoid counting blanks)

Thank you.

[Anonymous]

Thank you Verymuch Amit . your promt response helped a lot 🙂