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.   Account Interaction Type 1 call 1 inperson 2 call 3 inperson 3 call 3 msg   ...
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] )

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

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.