Count distinct values on a column, for values are greater than 1 with measure.

[Rafael_Almeida]

Hi everyone!

I have a question:

So, i have a column with distinct values wich i want count the repeated values except the values who the sum of agregattion is 1 or less with a measure. How can i make this measure?

An example:

Name of Product

Product 1

Product 2

Product 2

Product 1

Product 3

Product 1

Product 1

In this example, the count i want result is:

Name of product	Quantity
Product 1	4
Product 2	2

So, the measure can not count the product 3, because the agregattion sum of this item is one (1).

I hope you guide me!

Tahnk's for everything.

[AlexisOlson]

Try summarizing and then filtering like this:

NonSingleCount =
VAR Summary =
    SUMMARIZE (
        'Table',
        'Table'[Name of Product],
        "@Qty", COUNT ( 'Table'[Name of Product] )
    )
RETURN
    SUMX ( FILTER ( Summary, [@Qty] > 1 ), [@Qty] )

[Richard_100]

This one was really puzzling me but I was getting stuck using SUMMARIZECOLUMNS instead of SUMMARIZE as @AlexisOlson and @CerebusBI have used in their solutions.  Switching to SUMMARIZE has unlocked my own efforts which I thought I'd share for the further benefit of any passing readers

It uses SUMMARIZE to generate that temporary table (which I filter for [Count] > 1), SELECTCOLUMNS to keep only the one column that contains the product names (and therefore effectively becomes a list of products that pass the test), and feeding that list into a CALCULATE as a filter argument:

// Create a filtered table of all products with Count > 1 in the filter context:

VAR _Table =
   FILTER (
          SUMMARIZE (
              'Table',
              'Table'[Name of Product],
              "Count", COUNT ( 'Table'[Name of Product] )
            ), 
         [Count] > 1 )

// Keep only the first column of the above table, making it a list that can be fed into the CALCULATE modifier below:

VAR _List = SELECTCOLUMNS ( _Table, "Products", [Name of Product] )

// Use CALCULATE on the original table but filtering for product names that pass the Count > 1 test above by feeding the _List into the IN statement:

VAR _Calc =
  CALCULATE (
            COUNTX ( 'Table', 'Table'[Name of Product] ),
            KEEPFILTERS ( 'Table'[Name of Product] IN _List )
        )

RETURN
    _Calc

I'm not suggesting this is better than the other two solutions, but it may be of interest.

Regards

Richard

[Richard_100]

Hello

More sophisticated solutions may exist but you could use:

Measure = CALCULATE(COUNT('Table'[Name of Product]), FILTER('Table', COUNT('Table'[Name of Product])>1))

Hope that helps

Regards

Richard

[CerebusBI]

@Richard_100, both solutions (including my proposal below) seem to have the same problem: The Total includes the rows that have been filtered out: The Total is 7 - and it should be 6.

[Rafael_Almeida]

Hi, thank you both for support!

Yeah, the total continues remais at 7, but i will test anyway.

[Rafael_Almeida]

I tried, but the total impact the final measure, i don't understand the reason why the total results in 7 if we filtered thesers values in measure.

[CerebusBI]

The reason for that is somewhat complicated. Ultimately, it is because the sum is calculated separately internally.

Maybe it is easier not to filter in the measure, but to filter the visual through the measure.
I.e. by creating a very simple measure:

Count of Products = COUNT(Products[Name of product])

With the following result:

And than you can use it in the Filter pane (for the visual): "Count of Products" (Measure name) > 1.

After applying the filter, you should see the following:

Would this be sufficient?

[Rafael_Almeida]

Nice, work well is this case!

But, if i want a card visual, how can i filter this counts?

You are helping me a lot!

[CerebusBI]

Okay, you got me there.

I think you can't use a measure in the filter of a card visual.

I'll try to find a better solution.

[CerebusBI · Accepted solution]

@Rafael_Almeida,

I think, I have the solution.

A short recap

We have the following data:

If you create a simple measure to count the different products -- like this: Count of Products = COUNT(Products[Name of product]) -- you get the following table or card, respectively:

 

But you want to consider only the products whose number is greater than 1.

Therefore @Richard_100 and I have suggested adjusted measures.

Count of Products greater 1 =

VAR _Count =

COUNT(Products[Name of product])

RETURN

IF (

_Count = 1,

BLANK(),

_Count

)

Or:

Count of Products greater 1 Alternative =

CALCULATE (

COUNT(Products[Name of product]),

FILTER(

Products,

COUNT(Products[Name of product])>1

)

)

They both have the same effect: The rows with the internal filter context of product name are what we need, but the totals are wrong, because they still count the products which have only a number of 1:

Why is that

The reason for this behavior is that Totals always takes into account all rows of the underlying internally generated table, and does not take into account "internal" filters added by measures. (Actually, it's even more complicated than that).

Solution

With the following measure that uses the suggested measures, you get the desired behavior:

Count of Products greater 1 with correct total =

SUMX (

SUMMARIZE (

Products, Products[Name of product],

"Count", [Count of Products greater 1]),

[Count]

)

Instead of [Count of Products greater 1] you could also us [Count of Products greater 1 Alternative].

In both cases, you get the following results:

That was fun!

[Rafael_Almeida]

I'm impressed!!

This measure with correct total solved my problem!


Thanks!!

[CerebusBI]

Hi @Rafael_Almeida ,

Try this:

Count of Products greater 1 =

VAR _Count =

COUNT(Products[Name of product])

RETURN

IF (

_Count = 1,

BLANK(),

_Count

)

Is this what you meant?