Show amount of counts per ID

[Anonymous]

Hi guys,

I have the following columns:

I have a request ID where I cluster more requests under. I want to be able to show how many times per request ID a cluster request is added. 

For example: I want to see if my clustering is working properly. So if I see there has been no clustering it shows up as 100x 1 Cluster request. But if my clustering works properly it shows more then 1x per ID request.

A median of the amount of clusters works for me too. I just can't see to figure out how to set it up.

[cnweke]

Make a barchart with ID request as axis and count cluster request as value.

What it'll do is the following:

List out all the ID requests on the x-axis. Count how many cluster requests there are per ID request.

OR do it in a measure:

ClusteringCount: calculate(distinctcount(ID request),count(cluster request) > 1)

This tells you how many.

Marked as solved if this helped you 🙂

[Anonymous]

Thanks for the quick reply @cnweke 

Maybe I explained it unclear, sorry about that.

When I do it your way I see every single request ID with their times clustered request.

But I want to know how many times a cluster has happend. Not per request.

So for example I want to see Clustering 6 has happend 2x and clustering 4 has happend 3x 

[cnweke]

On the top of my head:

ClusteringCount: calculate(distinctcount(ID request),count(cluster request) > 1) and changing >1 into =2, =3 ... would work. This is messy though you'd need 4-5 measures in total. 

If I come up with a single measure that is fit for purpose I'll reply again. You can get started with this though.

[Anonymous]

@cnweke 

When trying to use the measure I get the following error:

ClusteringCount  = calculate(DISTINCTCOUNT(offers[ID Request]), count(offers[Cluster Request] > 1))

The COUNT function only accepts a column reference as an argument.

[cnweke]

ClusteringCount  = calculate(DISTINCTCOUNT(offers[ID Request]), count(offers[Cluster Request]) > 1)

 Your argument had to be fully in brackets

[Anonymous]

Sorry about that, wasn't paying attention.

I was getting the following error:

ClusteringCount = calculate(DISTINCTCOUNT(offers[ID Request]), count(offers[Cluster Request]) > 1)

A function 'COUNT' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

[cnweke]

(count(offers[Cluster Request]) > 1)

Don't worry, it's partially my fault.

Intellisense made me incapable of writing anything better than pseudocode 🙂 

[Anonymous]

ClusteringCount = calculate(DISTINCTCOUNT(offers[ID Request]), (count(offers[Cluster Request]) > 1 )) 

Unfortunatly this still provides the same error 😞

[v-shex-msft · Accepted solution]

HI @Anonymous ,

You can try to use following measure formula to get distinct count of 'ID Request' who has more than one correspond 'cluster request':

Measure =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            ALLSELECTED ( Table ),
            [ID Request],
            "DC", DISTINCTCOUNT ( Table[Cluster Request] )
        ),
        [DC] > 1
    )
)

Regards,

Xiaoxin Sheng

[cnweke]

ClusteringCount = calculate(DISTINCTCOUNT(offers[ID Request]), (count(offers[Cluster Request]) > 1) = TRUE)