Distinctcount filter by the result of a sum ?

[SylvainC]

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 ?

[PaulOlding]

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?

[SylvainC]

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

[PaulOlding]

@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.

[SylvainC]

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

[PaulOlding]

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

[SylvainC]

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

[DimaMD]

@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")

[SylvainC]

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