topic Re: Simple count certain values within measure in DAX Commands and Tips

Simple count certain values within measure

Anonymous — Tue, 24 May 2022 11:45:08 GMT

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)

Re: Simple count certain values within measure

Jihwan_Kim — Tue, 24 May 2022 12:59:50 GMT

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 ) )

Re: Simple count certain values within measure

Anonymous — Wed, 25 May 2022 00:42:08 GMT

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.

Re: Simple count certain values within measure

Jihwan_Kim — Wed, 25 May 2022 02:38:47 GMT

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.

Re: Simple count certain values within measure

Anonymous — Wed, 25 May 2022 11:40:48 GMT

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.

Re: Simple count certain values within measure

Jihwan_Kim — Thu, 26 May 2022 03:19:00 GMT

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.

Re: Simple count certain values within measure

Anonymous — Fri, 27 May 2022 03:55:52 GMT

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.

Re: Simple count certain values within measure

Jihwan_Kim — Fri, 27 May 2022 05:26:11 GMT

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" ?