Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors