Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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)
Solved! Go to 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.
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.
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.
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.
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
)
)
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.
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
55 | |
35 | |
21 | |
18 | |
15 |