Choose Aggregate function conditionally

[raksharh]

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"

Partner	Customer	Type
p1	c1	Agreement1
p1	c2	Agreement1
p2	c1	Agreement1
p2	c3	agreement2
p3	c2	agreement2
p4	c3	agreement2
p1	c3	agreement2

In the below case, I should get numbers like this

Agreement1	agreement2
3	2

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

[harshnathani · Accepted solution]

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]

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,