cancel
Showing results for
Did you mean:
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/2022 A 4/01/2022 A 5/01/2022 A 6/01/2022 A 7/01/2022 A 8/01/2022 A 9/01/2022 A 3/01/2022 B 4/01/2022 B 5/01/2022 B 6/01/2022 B 7/01/2022 B 3/01/2022 C 4/01/2022 C 5/01/2022 C 3/01/2022 D 4/01/2022 E 5/01/2022 F 6/01/2022 G 7/01/2022 H

Daily interim counts within a week are as below:

 Name_ 3-Jan 4-Jan 5-Jan 6-Jan 7-Jan 8-Jan 9-Jan Grand Total A 1 1 1 1 1 1 1 7 B 1 1 1 1 1 5 C 1 1 1 3 D 1 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
Super User

Hi,

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

``````Users count who have one in grand total measure 02: =
COUNTROWS (
FILTER (
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.

7 REPLIES 7
Frequent Visitor

Hi JiHwan 안녕하세요 지환님

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.

Super User

Hi,

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.

Frequent Visitor

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.

Super User

Hi,

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

``````Users count who have one in grand total measure 02: =
COUNTROWS (
FILTER (
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.

Frequent Visitor

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.

Super User

Hi,

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.

Super User

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

Announcements