Forum Discussion

raksharh's avatar
raksharh
Microsoft Employee
6 years ago
Solved

Choose Aggregate function conditionally

Hi Everyone,

 

I need to calculate count/DistinctCount based on a condition. 

For example in below table, when Type is "Agreement1", I should determine total count of "Customer" for "Agreement1". 

When Type is Agreement2, I should determine distinct count of "Customer" for "Agreement2"

 

PartnerCustomerType
p1c1Agreement1
p1c2Agreement1
p2c1Agreement1
p2c3agreement2
p3c2agreement2
p4c3agreement2
p1c3agreement2

 

In the below case, I should get numbers like this

Agreement1agreement2
32

 

Can you please let me know how can we acheive this through single DAX measure.

 

NOTE: I even have date columns, so I should be able to slice it accordingly through dates

 

Thanks,

Raksha

  • Anonymous's avatar
    Anonymous
    6 years ago

     

    Hi raksharh ,

     

    You can use the below measure:

     

    Count of Customer =
    var __disctinct = CALCULATE(DISTINCTCOUNT(Table1[Customer]),ALLEXCEPT(Table1,Table1[Type]))
    var __common = CALCULATE(COUNT(Table1[Customer]),ALLEXCEPT(Table1,Table1[Type]))
    return
    IF(TOPN(1,VALUES(Table1[Type]),VALUE(RIGHT(Table1[Type],1)),ASC) = "Agreement1", __common,__disctinct)
     
    This will only work for your above scenario. Incase you have more agreement types, the measure will not work.
    Logic is to find last digit of Type and convert in into number and then do a TOPN.
    So, Agreement 1 will be TOP1 when you do ASC and vice-versa.
     
    I think incase you have multiple agreement types go for multiple measures.
     
     
    Thanks and Regards,
    Harsh Nathani
     
     
     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

     

    Hi raksharh ,

     

    You can use the below measure:

     

    Count of Customer =
    var __disctinct = CALCULATE(DISTINCTCOUNT(Table1[Customer]),ALLEXCEPT(Table1,Table1[Type]))
    var __common = CALCULATE(COUNT(Table1[Customer]),ALLEXCEPT(Table1,Table1[Type]))
    return
    IF(TOPN(1,VALUES(Table1[Type]),VALUE(RIGHT(Table1[Type],1)),ASC) = "Agreement1", __common,__disctinct)
     
    This will only work for your above scenario. Incase you have more agreement types, the measure will not work.
    Logic is to find last digit of Type and convert in into number and then do a TOPN.
    So, Agreement 1 will be TOP1 when you do ASC and vice-versa.
     
    I think incase you have multiple agreement types go for multiple measures.
     
     
    Thanks and Regards,
    Harsh Nathani
     
     
     
  • ibarrau's avatar
    ibarrau
    Super User
    6 years ago

    Hi! If you just need those two measures let's see.

    I think this should be enough.

    Agreements1 =
CALCULATE (
    COUNT(Table[Customer]),
    Table[Type] = "Agreement1"
)

    In the second case 

    Agreements2 =
CALCULATE (
    DISTINCTCOUNT(Table[Customer]),
    Table[Type] = "agreement2"
)

    Hope this help,

    Regards,