Count Measure Values Ocurrences

[luisccmm]

Hi,

I have a very simple table with around 90thousand rows with 2 Columns, this is a sample that looks like.

There around 4 possible Status for each ID (Around 30thousand ID) whereas there around 90thousand rows in the table.

ID              Status

0001          STARTED

0001          ON GOING 

0001          FINISH

0002          STARTED

......

I need to COUNT how many ID have Just 1 Status, 2 Status.... and so on.

OUTPUT shoudl look like the following.

Count_status_ocurrences        Count IDS              ¿Meaning?

1                                              4.000                      There are 4K Ids that only have 1 Status (any  of STARTED, ON GOING...)

2                                              8.000                      There are 8K ids that have 2 status  (any combination of STARTED, ON GOING...)

3                                              8.000                      There are 8K ids that have 3 status (any combination of STARTED, ON GOING...)

4                                              10.000                    There are 10K that have all 4 status (any combination of STARTED, ON GOING...)

                                  TOTAL   30.000

How I should produce this table?

Thanks in advance.🙂

[Tahreem24]

@luisccmm ,

Try like below DAX Measure for counting:

Count = CALCULATE(DISTINCTCOUNT('Table'[Id]),ALLEXCEPT('Table','Table'[Status]))

[luisccmm]

Hi @Tahreem24  

Thanks for your quick response, I have something not clear about your proposal, because ¿What I should put on Dimension?

There are about  4 values for  Dimension Status:

STARTED

ONGOING

FINISHED

STANDBY

But I am NOT looking for counting Id for each of those (STARTED, ON GOING...).

What I need to count is How many IDs have 1 distinct status, how many have 2 distinct status, how many have 3 distinct status.... so on.

[v-yingjl · Accepted solution]

Hi @luisccmm ,

If you want to create only one measure to calculate the count of IDs which have different distinct status, I would suggest to create a calculated table first:

Distinct table = 
VAR tab =
    ADDCOLUMNS (
        ALL ( 'Table' ),
        "distinct status",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[Status] ),
                FILTER ( ALL ( 'Table' ), 'Table'[ID] = EARLIER ( 'Table'[ID] ) )
            )
    )
VAR tb =
    SUMMARIZE ( tab, [ID], [distinct status] )
RETURN
    tb

Then create this count measure and show the result in the table visual:

Count = COUNTROWS('Distinct table')

left table is the data table as a reference

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.