topic Re: Distinctcount filter by the result of a sum ? in DAX Commands and Tips

Distinctcount filter by the result of a sum ?

SylvainC — Wed, 28 Sep 2022 11:28:46 GMT

Hello everybody,

I have a table like this (with 67000000 rows ... and about 15000000 ClientID) :

ClientID	DomID	Contract
1	1	1
1	2	1
2	3	1
3	1	1
3	3	1

And i try to create a measure that gives me the number of customer that have only 1 contract

In my example : 1 (for ClientID 2)

And the second measure The number of customer that have more than one contract ...

in my example : 2 (for clientID 1 and 3)

In a calculate you can't use a filter based on the result of the sum so i don't find a solution for now ...

OnlyOneContract=CALCULATE(DISTINCTCOUNT(ClientID),FILTER(SUM(Contract)=1)

MoreThanOneContract=CALCULATE(DISTINCTCOUNT(ClientID),FILTER(SUM(Contract)>1)

Do you have any ideas to help me ?

Re: Distinctcount filter by the result of a sum ?

DimaMD — Wed, 28 Sep 2022 12:03:45 GMT

SylvainC Hi,
I think what needs to be done
1 calculate the number of contracts

Count_contract = COUNT('table (2)'[Contract])

2 Write a condition that will show which client has more than one contract

condition = IF( [Count_contract] = 1, "OnlyOneContract", "MoreThanOneContract")

Re: Distinctcount filter by the result of a sum ?

PaulOlding — Wed, 28 Sep 2022 12:14:33 GMT

Hi SylvainC

Here's a measure to get number of clients with 1 contract

OnlyOneContract = COUNTROWS( FILTER( ADDCOLUMNS( VALUES(Sheet1[ClientID]), "@Contracts", CALCULATE(SUM(Sheet1[Contract])) ), [@Contracts] = 1 ) )

The MoreThanOneContract measure is similar - filter for [@Contracts] > 1 instead.

With your volume of data you might need to consider performance and memory usage.

I tested on a dataset with 500,000 rows and 99,369 clients.

If we look at DAX Studio server timings you can see that a table with 99,369 rows needs to be constructed in memory. In your case that'll be a table with 15m rows.

One option that springs to mind is calculating this at refresh time. ie Make the contract count a calculated column rather than a measure. Whether that makes sense depends on how slicers & filters should impact the calculations. If someone were to slice on DomID = 2, would ClientID 1 now be in the OneContract or MoreThanOneContract bucket?

Re: Distinctcount filter by the result of a sum ?

SylvainC — Wed, 28 Sep 2022 12:20:54 GMT

Except that it's not working if you filter by Domain or attributes of the domaisn table ...

Re: Distinctcount filter by the result of a sum ?

SylvainC — Wed, 28 Sep 2022 12:42:57 GMT

It's contextual : You should filter and have different results when you put filters on Domain or attributes of domain ...

Re: Distinctcount filter by the result of a sum ?

PaulOlding — Wed, 28 Sep 2022 12:56:54 GMT

SylvainC OK, it needs to stay a measure then. I'd suggest trying out the measure I wrote above and seeing what the performance is like. You should try it out in PBI service too as the memory contraints will be different there than on your machine.

Re: Distinctcount filter by the result of a sum ?

SylvainC — Wed, 28 Sep 2022 13:23:52 GMT

But you can't visually filter a sum(measure) on a visual ...

Re: Distinctcount filter by the result of a sum ?

PaulOlding — Wed, 28 Sep 2022 14:07:31 GMT

SylvainC Could you explain in more detail what you're trying to do? I don't follow.

Re: Distinctcount filter by the result of a sum ?

SylvainC — Wed, 28 Sep 2022 20:50:38 GMT

with my example : I try to know over a lot of lines wich customer is a mono product owner (have a lonely domain) or a multi product owner (have a multi domain) but the filters must apply on the attributes of the domain (example Family or group : in text). If i take a group of domain, the filters have to apply across the whole groups selected ...

For example :

DomID	DomLib	Family
1	Bikes	Vehicles
2	Cars	Vehicles
3	Tomato	Vegetables