Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Dax Calculation for distinct Values

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

 

AccountInteraction Type
1call
1inperson
2call
3inperson
3call
3msg

 

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

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    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] )

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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.