Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Simple count certain values within measure

Hello

 

I am looking to count certain values within a measure using DAX formulas.

 

Below is a list of the distinct count of users per day:

Date                    Users

3/01/2022A
4/01/2022A
5/01/2022A
6/01/2022A
7/01/2022A
8/01/2022A
9/01/2022A
3/01/2022B
4/01/2022B
5/01/2022B
6/01/2022B
7/01/2022B
3/01/2022C
4/01/2022C
5/01/2022C
3/01/2022D
4/01/2022E
5/01/2022F
6/01/2022G
7/01/2022H

 

Daily interim counts within a week are as below:

Name_3-Jan4-Jan5-Jan6-Jan7-Jan8-Jan9-JanGrand Total
A11111117
B11111  5
C111    3
D1      1
E 1     1
F  1    1
G   1   1
H    1  1

 

Results:

Count "1" from the grand total = 5 (users)

Count "3" from the grand total = 1 (user)

 

  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Thank you for the link.

    Could you please try the below for calculating "appeared once" ?

     

    Users count who have one in grand total measure 02: = 
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( Data02, Data02[Date], Data02[Name] ),
            "@count", CALCULATE ( SUMX ( Data02, 1 ) )
        ),
        [@count] = 1
    )
)

     

    Please also check the attached pbix file. 

7 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pibx file.

     

     

    Users count who have one in grand total measure: =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( Data, Data[Users] ),
            "@count", CALCULATE ( SUMX ( Data, 1 ) )
        ),
        [@count] = 1
    )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi JiHwan 안녕하세요 지환님

    Thanks for your prompt reply.

    It does count some but appears that counts much less than expected.
    I wonder if it has anything to do with my actual dataset that has a time column where multiple users may appear multiple times on any given date. 

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Icon for Super User rankSuper User
      4 years ago

      Hi,

      Thank you for your feedback.

      Please share your sample pbix file's link that contains another dataset, with how the expected outcomes show.

      And then, I can try to look into it to come up with a more accurate solution.

      Thanks. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Jihwan,

         

        Here's the sample PBIX file that contains another dataset:
        https://1drv.ms/u/s!AjGre1-z2o5NgaVGXIJsJpsPjxirgQ?e=L5U1aS

         

        I am looking to count unique users per day so I can calculate how many users appeared once, twice, three times etc (0-7) from the daily unique user count weekly.