Forum Discussion

luisccmm's avatar
luisccmm
Helper II
5 years ago
Solved

Count Measure Values Ocurrences

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

 

Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    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.

3 Replies

  • Tahreem24's avatar
    Tahreem24
    Super User
    5 years ago

    luisccmm ,

    Try like below DAX Measure for counting:

    Count = CALCULATE(DISTINCTCOUNT('Table'[Id]),ALLEXCEPT('Table','Table'[Status]))
     
    • luisccmm's avatar
      luisccmm
      Helper II
      5 years ago

      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's avatar
        v-yingjl
        Community Support
        5 years ago

        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.