topic Re: Choose Aggregate function conditionally in DAX Commands and Tips

Choose Aggregate function conditionally

raksharh — Wed, 22 Apr 2020 10:51:22 GMT

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"

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

ibarrau — Wed, 22 Apr 2020 11:54:08 GMT

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,

Anonymous — Wed, 22 Apr 2020 13:19:54 GMT

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