Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have table which contains User_ID and Date Column from 1 st of Jan 2022 represent login activity on website
I Want to create a table which include date wise unique count of users who are completely fresh (never visited before)
Like on 2nd January there are 10 new user who are not visited in 1st of January or on 1st of March there are 7 new user who are not visited in last two month(Jan and Feb)
Please give me best and easy way to do it.
Thanks in advance
Solved! Go to Solution.
@speedramps Thanks for your time
But we required to create a line chart as per daily fresh inflow of user.Please find attached snap wrt required line chart with automatic uopdate the same unique count day wise.
Table1:
User ID | Date |
A | 1/1/2022 |
B | 1/1/2022 |
C | 1/1/2022 |
D | 1/1/2022 |
B | 1/2/2022 |
D | 1/2/2022 |
E | 1/2/2022 |
D | 1/3/2022 |
F | 1/3/2022 |
G | 1/3/2022 |
Output:
Date | Unique Count of user |
1/1/2022 | 4 |
1/2/2022 | 1 |
1/3/2022 | 2 |
Hi @ddpl ,
Here are the steps you can follow:
1. In Power query. Add Column – Index Column – From 1.
2. Create measure.
Flag =
var _1=CALCULATE(MAX('Table'[User ID]),FILTER(ALL('Table'),'Table'[Index]=MAX('Table'[Index])&&'Table'[Date]<=MAX('Table'[Date])))
var _2=CALCULATE(MAX('Table'[User ID]),FILTER(ALL('Table'),'Table'[Index]=MAX('Table'[Index])&&'Table'[Date]=MAX('Table'[Date])))
return
CALCULATE(DISTINCTCOUNT('Table'[User ID]), FILTER(ALL('Table'), _2 in SELECTCOLUMNS('Table',"id",_1)&&'Table'[Date]<=MAX('Table'[Date])))
Unique Count of user =
var _index1=MAXX(FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])),[Flag])
var _index2=MAXX(FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])-1),[Flag])
return
_index1 - _index2
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi Liu
Can I use below dax instead of yours?
Hi @ddpl ,
Here are the steps you can follow:
1. In Power query. Add Column – Index Column – From 1.
2. Create measure.
Flag =
var _1=CALCULATE(MAX('Table'[User ID]),FILTER(ALL('Table'),'Table'[Index]=MAX('Table'[Index])&&'Table'[Date]<=MAX('Table'[Date])))
var _2=CALCULATE(MAX('Table'[User ID]),FILTER(ALL('Table'),'Table'[Index]=MAX('Table'[Index])&&'Table'[Date]=MAX('Table'[Date])))
return
CALCULATE(DISTINCTCOUNT('Table'[User ID]), FILTER(ALL('Table'), _2 in SELECTCOLUMNS('Table',"id",_1)&&'Table'[Date]<=MAX('Table'[Date])))
Unique Count of user =
var _index1=MAXX(FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])),[Flag])
var _index2=MAXX(FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])-1),[Flag])
return
_index1 - _index2
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi Liu
Can I use below dax instead of yours?
@speedramps Thanks for your time
But we required to create a line chart as per daily fresh inflow of user.Please find attached snap wrt required line chart with automatic uopdate the same unique count day wise.
Table1:
User ID | Date |
A | 1/1/2022 |
B | 1/1/2022 |
C | 1/1/2022 |
D | 1/1/2022 |
B | 1/2/2022 |
D | 1/2/2022 |
E | 1/2/2022 |
D | 1/3/2022 |
F | 1/3/2022 |
G | 1/3/2022 |
Output:
Date | Unique Count of user |
1/1/2022 | 4 |
1/2/2022 | 1 |
1/3/2022 | 2 |
Hey @ddpl ,
can you provide an example file?
That would make it easier to provide a solution that works for you.
Best regards
Denis
I have data...
User ID | Date |
A | 01-01-22 |
B | 01-01-22 |
C | 01-01-22 |
D | 01-01-22 |
B | 01-02-22 |
D | 01-02-22 |
E | 01-02-22 |
D | 01-03-22 |
F | 01-03-22 |
G | 01-03-22 |
I want output as below...
Fresh Users | Date | |
4 | 01-01-22 | (A,B,C,D) |
1 | 01-02-22 | (E) |
2 | 01-03-22 | (F,G) |
Consider this solution,
Please click the thumbs up for me helping you.
And click Accept As Solution if it fixes your problem
Number of new customers =
/* Documentation
Get number of new customers as follows:-
Use addcolumns to get subset with (CustomerKey,Previous Rows)
Then filter the subset to just to include CustomerKeys with no previous rows.
Then count the number of Customers
*/
VAR mindate = MIN ( 'Calendar'[Date] )
VAR NewCustomers =
FILTER (
ADDCOLUMNS (
VALUES ( FactSales[CustomerKey] ),
"PreviousRows",
CALCULATE (COUNTROWS (FactSales ),
FILTER (ALL ( 'Calendar'[Date] ),'Calendar'[Date] < mindate))
),
[PreviousRows] = 0
)
RETURN
COUNTROWS(NewCustomers)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |