cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vocc242
Frequent Visitor

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)

 

1 ACCEPTED SOLUTION

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. 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

7 REPLIES 7
vocc242
Frequent Visitor

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. 

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. 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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. 

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. 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


First of all, thanks again for the updated pbix file.

The revised result is showing 17  
(14 users with "1" makes up 14 and 1 user with "3" = 17)
however, how do we count only the users with "1" to get 14.


Hi,

Thank you for your feedback.

Sorry that I cannot understond. Could you please kindly explain which user is "with 3", and what is the logic why it is "with 3" ?

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Jihwan_Kim
Super User
Super User

Hi,

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

 

Untitled.png

 

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.